PK and FK
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: PK and FK

  1. #1
    Join Date
    Jun 2000
    Posts
    315
    Can primary key, foreign key cross schema? Why or why not?
    For example: I have a table STUDENT under schema TEST with a primary key. And I have another table STUDENT1 under schema TEST1. Now I wanted to create a foreign key in table student1 reference test.student. Is this doable?

    Thanks!

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Hi,

    There is no problem creating a foreign key contraint from one schema to another providing the required privileges are granted.

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Jun 2000
    Posts
    315
    I thought so too. But the truth is: after I test it, it did not work. The error message is ora-00942: the referenced table test.student does not exist. user test1 has dba privs.

  4. #4
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Hi,

    I think you may need to grant the privileges directly.

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  5. #5
    Join Date
    Jun 2000
    Posts
    315
    What do you mean by 'grant the privileges directly'?

  6. #6
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Hi,

    Heres an example

    Schema Test1 has table employess
    Schema Test2 has table departments

    grant references, select on test2.departments to test1;

    then create the foreign key contraint from employess to departments.

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  7. #7
    Join Date
    Jun 2000
    Posts
    315
    Thanks, Jovery!

    After I grant the specific privs, it worked. The question is: why? Why dba privs does not work?
    The second question is: Can primary key, foreign key constraint cross database? For example, using database link?
    Why or why not?

    Thanks a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width