0

I have 2 different tables in two different databases. lets say, In DB1, I have table "A" with columns as "C1" as primary key, "C2", "C3". In DB2, I have table "B" with columns as "X1", "X2", "X3".

Now, I want to make a composite primary key in table B, such that fields are ("C1", "X1") as Composite_PK1, "X2", "X3".

Can anyone explain how to use other table's primary key as one of the elements of the composite primary key in another table?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
curiouscoder
  • 31
  • 7
  • 1
    Are the databases served via same daemon or are they on separate computers all together? Decrypted: is this on the same machine or not? – Mjh Apr 03 '17 at 13:22
  • In table B you do not have the columns of table A. You can only use them as reference of foreign key constraint. – quantummind Apr 03 '17 at 13:27
  • @quantummind but i want to take a column of Table A, and make the composite primary key with the actual primary key of Table B. – curiouscoder Apr 03 '17 at 13:34
  • @Mjh: Both the tables are in the different database, and both the database are located on the same server. – curiouscoder Apr 03 '17 at 13:37
  • @curiouscoder You can't. If you think about it, how would you tell the database which row in table DB2.B is related to which row in table DB1.A unless you have the data linking the two in table DB2.B? Ie. Suppose in table DB1.A you have two rows: `(c1, c2, c3) = (1, 2, 3), (4, 5, 6)` and in DB2.B you have two rows: `(x1, x2, x3) = ('A', 'B', 'C'),('D', 'E', 'F')` - how do you know which row in DB2.B matches to which row in DB1.A? You can only know that if DB2.B contains columns that also appear in DB1.A (a.k.a a foreign key). Because they're on different dbs, you can't enforce that fk though. – Boneist Apr 03 '17 at 14:15
  • @Boneist: Thanks, but if I have C1 in DB2.B, which is primary key in DB1.A, then How can I make the foreign key reference between them? – curiouscoder Apr 03 '17 at 14:21
  • You can't. You might be able to set up a trigger to do a select on DB1.A when inserting into DB2.B and another in DB1.A to check for rows in DB2.B before deleting the row/updating the pk value though. – Boneist Apr 03 '17 at 14:35
  • Possible duplicate of [Can I create Foreign Keys across Databases?](http://stackoverflow.com/questions/2972957/can-i-create-foreign-keys-across-databases) – Boneist Apr 03 '17 at 14:56

1 Answers1

1

A table's primary key must be made from a column or columns in the table itself, so your question doesn't really make sense.

If you have this table in the database called DB_ONE

A
   C1      PK
   C2
   C3

and this table in the database called DB_TWO

B
   C1    FK to column C1 in Table DB_ONE.A
   X1
   X2

you can define the primary key of table B as (C1, X1) if you wish. But the column C1 must be in table B. A particular column in any table can serve as both a foreign key (FK) and all or part of a primary key (PK).

If your tables are in different database schemas on the same Oracle instance you can try to use the schema-qualified table name (DB_ONE.A) when creating a foreign key. DDL like this might do the trick for you.

      ALTER TABLE DB_TWO.B
                  ADD CONSTRAINT fk_my_favorite_name
                     FOREIGN KEY (C1)
                      REFERENCES DB_ONE.A (C1);

If they are on different Oracle instances, you are out of luck trying to set up a foreign key.

O. Jones
  • 103,626
  • 17
  • 118
  • 172