Geek Answers Handbook

How to implement a conditional foreign key constraint?

I have the following table definition:

CREATE TABLE X ( A SOMETYPE NOT NULL, B SOMETYPE NOT NULL, C SOMETYPE NULL, PRIMARY KEY (A,B), FOREIGN KEY (A) REFERENCES Y (A) );

I want to add the following restriction: If C IS NOT NULL , then FOREIGN KEY (A,C) REFERENCES X (A,B) . How to do it (without using a trigger, if possible)?

I am using SQL Server 2008 R2, but this should not matter for the question.

+4
source share
2 answers

If I get what you need, you need to have a primary key on A in table Y and a unique constraint on A, B in table Y.

Try the following:

 create table Y ( A int not null, B int not null, primary key (A) ); create unique index IX_Y_AB on Y(A, B); create table X ( A int not null, B int not null, C int null, primary key (A, B), foreign key (A) references Y(A), foreign key (A, C) references Y(A, B) );

Test:

 insert into Y values (1, 2) insert into X values (1, 1, null) -- OK insert into X values (1, 2, 2) -- OK insert into X values (1, 3, 3) -- Fail
+1
source

I suspect you are thinking too much:

 CREATE TABLE X ( A SOMETYPE NOT NULL, B SOMETYPE NOT NULL, C SOMETYPE NULL, PRIMARY KEY (A,B), FOREIGN KEY (A) REFERENCES Y (A), FOREIGN KEY (A,C) REFERENCES X(A,B) );

If the reference column (for example, C ) is NULL, the foreign key will not be checked. So, if C is null, the second foreign key is not checked. But if C not null, then A,C must match the combination of A,B in the same table.

+1
source

More articles:


All Articles