Home » SQL & PL/SQL » SQL & PL/SQL » Can I create a FK from a schema user to another?
Can I create a FK from a schema user to another? [message #291465] Fri, 04 January 2008 05:23 Go to next message
trantuananh24hg
Messages: 668
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 #291469 is a reply to message #291465] Fri, 04 January 2008 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, if you have the appropriate privileges, see constraint section in SQL Reference.

Regards
Michel
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 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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 #291477 is a reply to message #291465] Fri, 04 January 2008 05:51 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
from the link Michel posted:

Quote:

To create a foreign key constraint, in addition, the parent table or view must be in your own schema or you must have the REFERENCES privilege on the columns of the referenced key in the parent table or view.


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 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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 #291486 is a reply to message #291484] Fri, 04 January 2008 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 04 January 2008 12:27

Yes, if you have the appropriate privileges, see constraint section in SQL Reference.

Regards
Michel


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 Go to previous messageGo to next message
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 #291493 is a reply to message #291492] Fri, 04 January 2008 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
- Never ever use SYS but for maintenance purpose (startup, shutdown, backup, recover)
- SYS is special
- SYS is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS" and you'll see the immediate answer)
- SYS does not act like any other user
- When you use SYS Oracle deactivates some code path and activates others
- Whatever you do with SYS will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS for anything that can be done by another user.
Use SYS ONLY for something that can't be done by someone else.


Regards
Michel
Re: Can I create a FK from a schema user to another? [message #291505 is a reply to message #291465] Fri, 04 January 2008 08:28 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Oh.k,
so instead of sys ,it should be which user who has rights to grant roles and privileges to other users?
System??
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous message
trantuananh24hg
Messages: 668
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.
Previous Topic: Sql to Update common rows in 2 tables
Next Topic: 'ORA-30487: ORDER BY not allowed here'
Goto Forum:
  


Current Time: Fri Dec 09 09:46:36 CST 2016

Total time taken to generate the page: 0.17191 seconds