SQL - Constraint between FK

Question

I am new in SQL and I have a very basic question.

In a SQL table having multiple FK pointing to the same foreign table, is it possible to create a constraint that imposes rules between the FK?

Here's an example of what I am trying to do :

The table 1 contains names and gender of a group of people NAME(PK), GENDER

The table 2 associates a group of 4 people to a room number. ROOM(PK), NAME (FK), NAME(FK), NAME(FK), NAME(FK)

How can constraint the FK in the second table to insure that only person of the same gender are associated to a room?

Is there a better way to deal with such scenario?

Thank you.

You can write a custom function that checks a room's member's genders, and then add a constraint to the room table to call that function. See [this question](http://stackoverflow.com/questions/2588072/how-do-i-create-a-multiple-table-check-constraint) for an example. — Blorgbeard, Oct 16 '14 at 19:10

Ankit Vora · Answer 1 · 2016-09-02T14:56:45.807

I would not have separate columns for 4 members of the room. I would instead have one Name column and one Room column and group by Room to get the 4 members and then check their genders. If you want to continue with your solution then you can add a trigger to check if the genders are same if not fail the insert.

Note: This trigger will only work if only one row is inserted at a time.

CREATE TRIGGER Production.tr_ForeignTable_gender
ON [ForeignTable]
AFTER INSERT AS
BEGIN
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
declare @Name1 varchar(200), @Name2 varchar(200), @Name3 varchar(200),@Name4 varchar(200), @Gender1 varchar(10),@Gender2 varchar(10),@Gender3 varchar(10),@Gender3 varchar(10)

select @Name1 = Name1, @Name2 = Name2, @Name3 = Name3, @Name4 = Name4
from Inserted

select @Gender1 = Gender from [PrimaryTable] where Name = @Name1

select @Gender2 = Gender from [PrimaryTable] where Name = @Name2

select @Gender3 = Gender from [PrimaryTable] where Name = @Name3

select @Gender4 = Gender from [PrimaryTable] where Name = @Name4

if @Gender1 != @Gender2 or @Gender2 != @Gender3 or @Gender3 != @Gender4
BEGIN
THROW 50000, 'Genders cannot be same', 0;
END;
END;
GO

SQL - Constraint between FK

1 Answers1