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.
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()
declare @ret int
select @ret=max(colB) from TableB
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.