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: Sunday, October 19, 2014 10:29 AM
Points: 1,880, Visits: 3,460
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
Posted Friday, July 16, 2010 12:54 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:27 PM
Points: 1,945, Visits: 3,068
Full ANSI/ISO Standards SQL does allow any predicate in a CHECK() constraint. SQL Server and most other products have not gotten that far yet.

The trick is to use an updatable VIEW with a WITH CHECK OPTION and hide the base table from the users. Example: no department can have more than 100 people in it.

CREATE VIEW Personnel_2
AS
SELECT P1.*
FROM Personnel AS P1
WHERE NOT EXISTS
(SELECT *
FROM Personnel AS P2
GROUP BY department_nbr
HAVING COUNT(*) > 100
WITH CHECK OPTION;



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #954115
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse