0

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.

0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.