Foreign Key Constraints with multiple keys

Question

I have knocked up a simple table design:

Ass you can see, tAttachments has a foreign key for Images, PlayerNames and PlayerNumbers. Here is a closer look at the table:

I need to create a constraint on the table which states that tAttachments must have at least 1 foreign key set. Does anyone know how I can do that?

Possible duplicate of [I need a check constraint on two columns, at least one must be not null](http://stackoverflow.com/questions/26102456/i-need-a-check-constraint-on-two-columns-at-least-one-must-be-not-null) — Sean Lange, Feb 11 '16 at 15:43
mine is not the same as that, mine requires that only 1 can be set. so 1, 0, 0 is fine; 0, 1, 0 is fine, 0, 0, 1 is fine, but 1,1, 0 is not nor is 1, 1, 1 or 0, 0, 0. I hope that makes sense. @PieroAlberto it is MSSQL so I am using SQL Management Studio. — r3plica, Feb 11 '16 at 17:47

score 0 · Answer 1 · answered Feb 12 '16 at 07:22

I don't have any idea how to do it in the db design step, but you can do a trigger INSERT and a trigger UPDATE. In this way you can check if this constraint is respected.

Something like:

create trigger tAttachmentsCheck on tAttachments for insert, update as
begin
if exists (select *
    from inserted i
    where (i.ImageId <> NULL and PlayerNameId <> NULL)
    or (i.ImageId <> NULL and PlayerNameId <> NULL and PlayerNumberId <> NULL)
    or (i.ImageId <> NULL and PlayerNumberId <> NULL)
    or (i.PlayerNumberId <> NULL and PlayerNameId <> NULL))

    rollback transaction
end

score 0 · Answer 2 · answered Feb 13 '16 at 13:31

A straightforward check constraint:

ImageID is NULL and PlayerNameID is NULL and PlayerNumberID is NOT NULL
or ImageID is NULL and PlayerNameID is NOT NULL and PlayerNumberID is NULL
or ImageID is NOT NULL and PlayerNameID is NULL and PlayerNumberID is NULL

Foreign Key Constraints with multiple keys

2 Answers2