CODEWITHSUNDEEP
sqloracle-databaseforeign-keysprimary-keycomposite-primary-key
ditmark12
ditmark12

Reputation: 121

Can I use a compound primary key as foreign key in Oracle?

I just wanted to know if it is possible to use table A's compound primary key within table B as a foreign key.

CREATE TABLE A(
   ID INT,
   COD INT,
   CONSTRAINT  PK_INF PRIMARY KEY (ID, COD),
);

CREATE TABLE B(
   REF_NUMBER INT,
   NAME VARCHAR(30),
   CONSTRAINT  FK_INF FOREIGN KEY (REF_NUMBER) REFERENCES A(PK_INF) <- is this correct?
);

Thanks in advance.

Upvotes: 0

Views: 113

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

The answer to your question is "yes", you can use a compound primary key for a foreign key reference. But, you need two columns for it. Something like this:

CREATE TABLE A(
   ID INT,
   COD INT,
   CONSTRAINT  PK_INF PRIMARY KEY (ID, COD),
);

CREATE TABLE B(
   REF_ID INT,
   REF_CODINT,
   NAME VARCHAR(30),
   CONSTRAINT  FK_INF FOREIGN KEY (REF_ID, REF_COD) REFERENCES A(ID, COD) <- is this correct?
);

Upvotes: 1

Related Questions