CODEWITHSUNDEEP
sqloracle-databaseforeign-keys
kirax
kirax

Reputation: 143

Foreign Key references 2 separate tables

I have 2 tables both with primary ids:

CREATE TABLE Table1
(   Id  INT NOT NULL AUTO_INCREMENT,
CONSTRAINT t1_pkey PRIMARY KEY (Id));

CREATE TABLE Table2
(   Id  INT NOT NULL AUTO_INCREMENT,
CONSTRAINT t2_pkey PRIMARY KEY (Id));

I have a third table which I am trying to setup a foreign key

CREATE TABLE Action
(   TableId INT NOT NULL AUTO_INCREMENT,
CONSTRAINT ac_pkey PRIMARY KEY (Id));

I need to add a foreign key that can reference either table1 or table2 depending on which one has value. Is this possible or am I going to have to setup a parent table for tables 1 and 2?

Upvotes: 1

Views: 906

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

It is not possible for a foreign key to reference one table or the other.

  • You could combine table1 and table2 with a type column and then have the combination of id, type be the primary key of the combined table and the foreign key in Action.
  • You could create a new table that is a parent of both table1 and table2 as well as Action
  • You could create two separate columns in Action, on that references table1 and the other that references table2 and then create a check constraint that ensures that only one of those is populated.

Which approach you prefer will come down to exactly what you're trying to model.

Upvotes: 2

Related Questions