CODEWITHSUNDEEP
sql-server
Alim
Alim

Reputation: 337

How to enforce a check on 2 tables

I was wondering what would be the best way to enforce a check on the ItemId of table 2. The point of the check would be to enforce that the row in table 3, where table 2 is linked to has the same ItemId just like in the example.

Edit: After ruud's comment I realized I might need to add a bit more detail since the current state is a bit too general.

Table2 is a link table in which Table4 and ItemTable come together, this table is also linked to Table3 but this is nullable. The PK in Table2 is a combination of T4ID and ItemId.

Relationships:

ItemTable to Table2 is one(ItemTable) to many(Table2).

ItemTable to Table3 is one(ItemTable) to many (Table3).

Table2 to Table3 is one(Table3) to many(Table2).

Table2 to Table4 is one(Table4) to many(Table2)

ItemTable          Table2                         Table3              Table4
ItemId(PK)         T4ID(PK FK)                    T3ID(PK)            T4ID(PK)
                   ItemId(PK FK) <- should match ->  ItemId(FK)
                   T3ID(FK)

Upvotes: 1

Views: 33

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239754

You can enforce this by declaring a "superkey" on Table3 that includes both columns T3ID and ItemID (usually, this would be a Unique Key Constraint/Index).

You would then also declare an additional1 FK between tables 2 and 3 on both T3ID and ItemID.

I.e. leave all existing constraints as they are. Then run:

ALTER TABLE Table3 ADD CONSTRAINT UQ_Table3_Items UNIQUE (T3ID, ItemId);
ALTER TABLE Table2 ADD CONSTRAINT FK_Table2_ItemXRef
   FOREIGN KEY (T3ID, ItemId) references Table3 (T3ID, ItemID);

1Technically this then makes the plain FK based on T3ID redundant, but I usually prefer to leave it present to represent the "real" Foreign Key.

Upvotes: 2

Related Questions