Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737

    Unanswered: Foreign key accross different databases

    I know that as of about 5 years ago, one could not create a foreign key accross tables in another database (parent and child in different databases). Is this still the case in the latest release of Oracle?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    When you say "database" you mean an Oracle instance?
    If that is the case, then this is still not possible.

    But multiple instances are rarely used in Oracle (only e.g. to separate development, test and production environments).

    When you want to achieve something similar as with databases in SQL Server, then you should use schemas (i.e. users). Having FKs across schemas is certainly possible

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Yes, I meant 2 different databases (instances) that reside on two different physical servers.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    The only workaround I can think of:

    In the database which references the other table, create a materialized view over a dblink to the remote server. Then create a foreign key from the referencing table to the materialized view.

Posting Permissions

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