CODEWITHSUNDEEP
oracle-databaseforeign-keys
Safi Ullah Ibne Sultan
Safi Ullah Ibne Sultan

Reputation: 622

creating composite foreign key from two different tables primary keys in oracle

table1:

tid(primary key) // no foreign keys here

table2:

sid(primary key) // no foreign keys here too

table3:

 Tid
 Sid
 iid(primary key)
 foreign key(Tid,Sid) references table1(tid).table2(sid)

 In table3 i want to make a composite foreign key or composite foreign key constraint but failed . there are many questions related to this .But none of them seems helpful to me . How can i do that ? Is it valid ? Then what is the syntax of making composite foreign key from two different tables primary key

Upvotes: 4

Views: 2351

Answers (1)

Carlo
Carlo

Reputation: 1579

It's not possible to have a single foreign key referencing fields on different tables, and it makes no sense at all. A foreign key of two or more fields implies that the combination of values of the fields must be match on a single record of the referenced table, and this can't be done if the referenced fields are on different tables.

What you can do is to create two distinct foreing keys to the two tables, as following:

CREATE TABLE table3(
    iid NUMBER,
    Tid NUMBER,
    Sid NUMBER,
    CONSTRAINT pk    PRIMARY KEY (iid) USING INDEX TABLESPACE idx,
    CONSTRAINT fk001 FOREIGN KEY (tid) REFERENCES table1(tid),
    CONSTRAINT fk002 FOREIGN KEY (sid) REFERENCES table2(sid)
);

Upvotes: 1

Related Questions