Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    106

    Unanswered: Can we set foreign key constarint to a table in another database

    Hi,

    I have 2 databases db1 and db2.

    I have dept table in db1 and emp table in db2.

    create table dept(deptno number,dname varchar2(10))

    Can I set foreign key constraint for the emp table in db2 to dept table in db1.
    create table emp(empno number,deptno number)

    alter table emp add constraint fk1 foreign key references db1.dept(deptno)

    This did not work.
    Any solutions? or this is impossible ?
    Sachi

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    How about creating a database link and referencing the table through it?

  3. #3
    Join Date
    Jan 2003
    Posts
    106
    db links doesnt allow DDL operations
    Sachi

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    The child and parent tables must be on the same database. They cannot be on different nodes of a distributed database. Oracle allows you to enable referential integrity across nodes of a distributed database with database triggers. For information on how to use database triggers for this purpose, see the "Using Database Triggers" chapter of the Oracle8 Server Application Developer's Guide.
    I didn't read the specified chapter; perhaps you could use it to solve the problem ...

Posting Permissions

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