Menu
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?
806
(Note: foreign keys can be composite keys, so the foreign key for one column could be two or more columns in another table.
The foreign key is the anchor on the many side of a one-to-many (1:M) relationship, much as the primary or candidate key is the anchor on the one side of this relationship.
When you join the two tables together, the primary key of the parent table will be set equal to the foreign key of the child table. Whichever one is not the primary key is the foreign key. In one-to-many relationships, the FK goes on the "many" side.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
