CODEWITHSUNDEEP
sql
appsecguy
appsecguy

Reputation: 1049

Foreign Key for either-or column?

Is it possible to have a foreign key that requires either column A or column B to have a value, but not both. And the foreign key for column A matches Table 1 and the foreign key for column B matches Table 2?

Upvotes: 5

Views: 3266

Answers (3)

Joe Enos
Joe Enos

Reputation: 40413

A check constraint can handle this. If this is SQL Server, something like this will work:

create table A (Id int not null primary key)
go
create table B (Id int not null primary key)
go
create table C (Id int not null primary key, A_Id int null, B_Id int null)
go
alter table C add constraint FK_C_A
foreign key (A_Id) references A (Id)
go
alter table C add constraint FK_C_B
foreign key (B_Id) references B (Id)
go
alter table C add constraint CK_C_OneIsNotNull
check (A_Id is not null or B_Id is not null)
go
alter table C add constraint CK_C_OneIsNull
check (A_Id is null or B_Id is null)
go

Upvotes: 5

Dan Pichelman
Dan Pichelman

Reputation: 2332

It depends on which database you're working with. If you want a table Foo that has FK relationships to Table1 and to Table2 but only one at a time, then you'll need to set up either some sort of trigger (my links assume SQL Server, but the ideas's the same) or Constraint to enforce your rule that only one column have a value.

Upvotes: 2

veer1987
veer1987

Reputation: 83

it is not necessary that a column have values in it at that time of applying foreign key,but the column name would be same and the data types as well.

Upvotes: 1

Related Questions