CHECK constraint

  • Hi, I am new to SQL databases and have been having trouble doing the following. I need to check that the the maximum value in one column of one table does not exceed the maximum value of one column in another table using a CHECK constraints. How do I do that if it does not allow me to use the MAX() function? The error message complains about the use of an aggregate function.

    I can do it with a TRIGGER but is required to do it using a CHECK.

    Help!!!

  • index_us (7/10/2010)


    Hi, I am new to SQL databases and have been having trouble doing the following. I need to check that the the maximum value in one column of one table does not exceed the maximum value of one column in another table using a CHECK constraints. How do I do that if it does not allow me to use the MAX() function? The error message complains about the use of an aggregate function.

    I can do it with a TRIGGER but is required to do it using a CHECK.

    Help!!!

    You can create a scalar value function that returns the max value from TableB. In TableA you create a check constraint that looks something like this (function also included):

    create function GetMaxFromTableB()

    returns int

    as

    begin

    declare @ret int

    select @ret=max(colB) from TableB

    return @ret

    end;

    alter table TableA add constraint chkFunnyCheck check (colA<=dbo.GetMaxFromTableB());

    Just keep in mind that this is not bullet proof as the check constraint in TableA will not check values for existing rows if you do modifications in colB on TableB. This can only, in my knowledge, be accomplished by using a trigger in TableB that checks if a update or delete breaks your rule.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply