SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Monday, July 31, 2006

Constraint on an aggregate?

SQL Apprentice Question
Probably not a great idea, but is it possible to create a constraint on
an aggregate of rows? E.g. "sum(amount) = 0 group by foreign_key"


Celko Answers
Try this little trick:

CREATE TABLE Foobar
(..);


CREATE VIEW Foobar2 ( ..)
AS SELECT ( ..)
FROM Foobar
WHERE (NOT EXISTS
(SELECT *
FROM Foobar
GROUP BY foreign_key
HAVING SUM(amount) <> 0)
WITH CHECK OPTION;

No comments: