Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CHECK constraint Expand / Collapse
Author
Message
Posted Saturday, July 10, 2010 9:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 10, 2010 1:27 PM
Points: 3, Visits: 3
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!!!
Post #950355
Posted Saturday, July 10, 2010 12:10 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 29, 2015 5:34 AM
Points: 1,908, Visits: 3,510
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.
Post #950359
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse