CODEWITHSUNDEEP
sqlsql-serversql-server-2008foreign-keysconstraints
Marcus
Marcus

Reputation: 9439

SQL Server foreign key constraint - pair (same tuple)

Suppose these 2 tables:

tbl1

ID
name
fkID1
fkID2

tbl2

ID
pkID1
pkID2

Whenever data is inserted into tbl1, how can I ensure (fkID1, fkID2) exists as a row (tuple) in tbl2 in the form (pkID1, pkID2)?

I have no problem making a constraint so fkID1 must exist and that fkID2 must exist but I want to ensure they exist in the same row.

Either via SQL or instructions to create it via management studio would be great.

Upvotes: 1

Views: 3060

Answers (1)

Eric
Eric

Reputation: 95133

You can just comma delimit the columns you want to check. Just make sure they're in order.

alter table tbl1 with check 
add constraint FK_tbl1_tbl2 
foreign key (fkID1, fkID2) 
references tbl2 (pkID1, pkID2)

Upvotes: 5

Related Questions