Can I create a FK from a schema user to another? [message #291465] |
Fri, 04 January 2008 05:23  |
trantuananh24hg
Messages: 744 Registered: January 2007 Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Hi all!
I have one table named as TEMP with POST_ID, POST_LOCALE, POST_EMP_ID columns which is belonged to user MANAPOST. Another user LOCAL has one table including table POST with POST_ID column.
Can I create a FK from MANAPOST.TEMP (POST_ID) to LOCAL.POST(POST_ID)?
Thank you!
|
|
|
|
Re: Can I create a FK from a schema user to another? [message #291473 is a reply to message #291469] |
Fri, 04 January 2008 05:40   |
trantuananh24hg
Messages: 744 Registered: January 2007 Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Thank you, Michel!
But whenever I test on my DB with 2 schema users SCOTT and TEMP, they have got the same tables, EMP, DEPT, the same constraints...because I imported anything from SCOTT to TEMP.
I can not create a FK from table EMP (SCOTT) to table DEPT (TEMP).
SQL> show user
USER is "SCOTT"
SQL> select * from temp.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> alter table EMP
2 add constraint FK_DEPT_TEMP
3 foreign key(deptno)
4 references temp.dept(deptno);
references temp.dept(deptno)
*
ERROR at line 4:
ORA-00942: table or view does not exist
SQL>
Thank you!
[Updated on: Fri, 04 January 2008 05:43] Report message to a moderator
|
|
|
|
Re: Can I create a FK from a schema user to another? [message #291484 is a reply to message #291477] |
Fri, 04 January 2008 06:11   |
trantuananh24hg
Messages: 744 Registered: January 2007 Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Thank you for your reply!
Of course, I connected as SCOTT, every table which are belonged to SCOTT are mine.
The parent table is EMP, and SCOTT user was granted DBA role
SQL> select grantee, granted_role from dba_role_privs
2 where grantee='SCOTT';
GRANTEE GRANTED_ROLE
------------------------------ ------------------------------
SCOTT RESOURCE
SCOTT DBA
SCOTT CONNECT
SQL> select constraint_name CNS_NAME,
2 constraint_type CNS_TYPE,
3 table_name TB_NAME
4 from user_constraints
5* where owner='SCOTT'
SQL> /
CNS_NAME CNS_TYPE TB_NAME
------------------------------ ------------------------------ ----------------
------------
FK_DEPTNO R EMP
PK_EMP P EMP
PK_DEPT P DEPT
SQL>
But
SQL> alter table EMP
2 add constraint FK_TEMP foreign key(deptno)
3 references temp.dept(deptno);
references temp.dept(deptno)
*
ERROR at line 3:
ORA-00942: table or view does not exist
Same to adding a constraint from one user to another, can I add a constraint from a table to a view belonged to another schema users?
Thank you!
|
|
|
|
Re: Can I create a FK from a schema user to another? [message #291492 is a reply to message #291465] |
Fri, 04 January 2008 06:42   |
varu123
Messages: 754 Registered: October 2007
|
Senior Member |
|
|
YOu need to grant the references privilege on the parent table
SQL> alter table EMP
2 add constraint FK_TEMP foreign key(deptno)
3 references temp.dept(deptno);
SQL> conn / as sysdba;
SQL> grant references on temp.dept to local;
Now connect as local and issue the alter table statement.
|
|
|
|
|
Re: Can I create a FK from a schema user to another? [message #291508 is a reply to message #291465] |
Fri, 04 January 2008 09:10   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Quote: | so instead of sys ,it should be which user who has rights to grant roles and privileges to other users?
|
The owner of the table (TEMP user in this case) is privileged enough to grant rights on its own objects (tables in this case).
There is no need to use any special user (SYS/SYSTEM).
|
|
|
Re: Can I create a FK from a schema user to another? [message #291561 is a reply to message #291508] |
Fri, 04 January 2008 21:27  |
trantuananh24hg
Messages: 744 Registered: January 2007 Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
Thank all!
I am reading again, again the referent document which is presented by Michel!
Michel and flyboy are right, do not use SYS/SYSTEM in many cases, only using it for maintaining Database.
varu123 |
SQL> grant references on temp.dept to local;
|
Thank you, varu, but in this case, both of SCOTT and TEMP are granted sufficient privilege.
|
|
|