Adding field constraints on child tables based on parent table

Question

I need to add constraints to a particular field in all the entries of a child table with a foreign key to a single row in parent table. What is the best way to do it?

For example, my parent table has

    Id Money Name
    -- ----- ----
     1  30    xyz
     2  40    abc

And the table referencing this

    Id Cost Person
    -- ---- ------
     1  10    1
     2  10    1
     3  20    2

Here Person is the foreign key and I need to put constraint in Cost based on Money, for instance, the sum of all cost should be always lesser or exactly equal to the total Money.

score 0 · Accepted Answer · answered Feb 19 '16 at 19:40

A CHECK constraint can only reference the table or column it's attached to. You'll have to validate the values as part of the query that's inserting into the referencing table. Something like this:

DECLARE @Cost INT
DECLARE @PersonID INT

INSERT INTO ChildTable
        ( cost,person )
SELECT @Cost
       ,@PersonID
FROM ParentTable PT
WHERE pt.id = @PersonID
      AND ((SELECT SUM(cost) FROM ChildTable CT WHERE CT.id = PT.id) + @Cost) < PT.money

You could also create a DELETE trigger that queries the other table and immediately removes the row if it fails the criteria. However that is a very sub-optimal choice.

Adding field constraints on child tables based on parent table

1 Answers1