CODEWITHSUNDEEP
sql
isekaijin
isekaijin

Reputation: 19772

How to implement a "conditional foreign key" restriction?

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 constraint: If C IS NOT NULL, then FOREIGN KEY (A,C) REFERENCES X (A,B). How do I do that (without using a trigger, if possible)?

I am using SQL Server 2008 R2, but this should be irrelevant to the question.

Upvotes: 4

Views: 1291

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

I suspect you're overthinking things:

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 a referencing column (e.g. C) is null, then the foreign key will not be checked. So, if C is null, the second foreign key isn't checked. But if C is not null, then A,C has to match an A,B combination in this same table.

Upvotes: 2

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

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

Try this:

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

Upvotes: 2

Related Questions