CODEWITHSUNDEEP
sqlsql-serverforeign-keys
Will
Will

Reputation: 3

SQL Server One Foreign Key From 2 Tables

Is it possible to have one foreign key that can reference 2 different tables?

Here's what I mean...

Create Table1(id nvarchar primary key);

Create Table2(id nvarchar primary key);

Create Table3(id nvarchar foreign key references (Table1(ID) or Table2(ID))

If it is not possible, is there a recommended way to work around this?

Upvotes: 0

Views: 146

Answers (2)

The Impaler
The Impaler

Reputation: 48865

Yes, you can have a column (or tuple of columns) to reference multiple tables as foreign keys. I [personally] call them "forking foreign keys", but that's just me.

However, those are TWO separate constraints that are enforced separately, each one all the time. You can't have an OR logic on it.

For example.

create Table1 (id nvarchar primary key);

create Table2 (id nvarchar primary key);

create Table3 (
  id nvarchar,
  constraint fk1 foreign key (id) references Table1 (ID),
  constraint fk2 foreign key (id) references Table2 (ID)
);

If you need an OR condition, then the answer to your question is "no".

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I am guessing that you want disjoint references to the first two tables.

You can come pretty close with persisted computed columns:

Create Table1 (
    id nvarchar(255)
);

Create Table2 (
    id nvarchar(255)
);

Create Table3 (
    id nvarchar(255),
    type int not null check (type in 1, 2),
    id_1 as (case when type = 1 then id end) persisted,
    id_2 as (case when type = 2 then id end) persisted,
    foreign key (id_1) references table_1(id),
    foreign key (id_2) references table_2(id)
);

Here is a db<>fiddle. This shows the error when the id does not properly reference the specified table.

Upvotes: 2

Related Questions