Creating a check constraint for values in another table with nulls

Question

I have two tables, A and B. The structure looks like this:

CREATE TABLE A (
    w int NOT NULL,
    x int NOT NULL,
    y int NOT NULL,
    CONSTRAINT PK_A PRIMARY KEY (w, x, y)
)

CREATE TABLE B (
    w int NOT NULL,
    y int NULL,
    z int NOT NULL
)

I want to make sure for any set of values entered in table B, that w and y are in table A. If the y value in table B is null, I only want to make sure that w is in table A.

Some sample data, inserts, and expected results:

Table A
w  x  y
----------
1  1  1
1  1  2
1  2  1
1  3  2
2  1  1

INSERT INTO B (w, y, z) VALUES (1, 1, 3) -- good
INSERT INTO B (w, y, z) VALUES (1, NULL, 3) -- good
INSERT INTO B (w, y, z) VALUES (1, 1, 4) -- good
INSERT INTO B (w, y, z) VALUES (2, NULL, 3) -- good
INSERT INTO B (w, y, z) VALUES (1, 3, 1) -- fail
INSERT INTO B (w, y, z) VALUES (3, NULL, 1) -- fail

Any way for this to work? I'm using SQL Server 2000 if that comes into play.

`If the y value in table B is null, I only want to make sure that y is in table A.`. Didn't get this statement! If y value is null in B, what value of 'y' should be in table A? — Vikdor, Aug 30 '12 at 16:02
Thanks, you caught a typo for me. It should be that if y is null in table B, I want to check to make sure **'w'** is in table A. — Dave, Aug 30 '12 at 16:08
Unless I'm misunderstanding something, isn't this just two FK constraints? `B.W -> A.W` and `B.Y -> A.Y`? — Mike Christensen, Aug 30 '12 at 16:15
Not quite. If if B.Y is not null, it's B.W, B.Y -> A.W, A.Y (assuming I'm using the correct notation). If it were two separate FKs, it would allow `INSERT INTO B (w, y, z) VALUES (2, 2, 1)` which should fail because there isn't a tuple in table A with w=2 and y=2. — Dave, Aug 30 '12 at 16:41
Ah yes, right you are. Yea I think FK constraints don't really allow any sort of "complex" logic. Maybe in some future SQL standard! — Mike Christensen, Aug 30 '12 at 17:28

Jordão · Accepted Answer · 2012-08-30T17:56:10.017

2

Unfortunately, you cannot use a foreign key constraint on B.w and B.y because they would reference non-unique columns on A. But you can add this check via triggers:

create trigger check_w on B for insert, update
as
if not exists(select * from A join inserted on A.w = inserted.w) 
begin
  raiserror('W not in A!', 1, 1)
  rollback transaction
end

GO

create trigger check_y on B for insert, update
as
if 
(select y from inserted) is not null and 
not exists(select * from A join inserted on A.y = inserted.y) 
begin
  raiserror('Y not null and not in A!', 1, 1)
  rollback transaction
end

GO

You can definitely combine these two triggers in one.

Also note that you'll need a trigger on A for the delete operation. You either prevent deletion if there're matching rows on B, or you perform a cascade delete operation.

edited Aug 30 '12 at 17:56

answered Aug 30 '12 at 16:44

Jordão

55,340
13
112
144

It isn't exactly what I was looking for in terms of the constraints, but I didn't explain it very well apparently. I can combine your triggers and make the if statement: `if not exists(select * from A join inserted on A.w = inserted.w and (A.y = inserted.y or inserted.y is null))` and that matches what I'm looking for. Unfortunately it only works on insert/update of B. There's nothing keeping me from adding something to B, then deleting the row from A. Any way to do this with a check constraint maybe? – Dave Aug 30 '12 at 17:36
Oh yeah, you'll need another trigger on A (on delete...), I updated accordingly.... – Jordão Aug 30 '12 at 17:55
I ended up using this method. I did combine the trigger as I mentioned to meet my requirement. I also added a trigger on A for both update and delete. Thanks for the help. – Dave Sep 04 '12 at 17:18

Creating a check constraint for values in another table with nulls

1 Answers1