I'm working with a system where a Quote can be created for a fixed amount of money, and items added to it later by other users. The sum of those item amounts should of course never exceed the quote amount.
CREATE TABLE Quote ( QuoteId INT IDENTITY PRIMARY KEY, QuoteAmount DECIMAL(9,2) NOT NULL ); CREATE TABLE QuoteItem ( ItemId INT IDENTITY PRIMARY KEY, QuoteId INT NOT NULL, ItemAmount DECIMAL(9,2) NOT NULL, CONSTRAINT FK_QuoteItem_Quote FOREIGN KEY (QuoteId) REFERENCES Quote(QuoteId), );
In the above, for each QuoteId, SUM(QuoteItem.ItemAmount) should always be <= Quote.QuoteAmount.
In other words, the following should never return any results:
SELECT q.QuoteId FROM Quote q INNER JOIN QuoteItem qi ON q.QuoteId = qi.QuoteId GROUP BY q.QuoteId, q.QuoteAmount HAVING SUM(qi.ItemAmount) > q.QuoteAmount;
The only way I know to enforce this as a constraint is to:
a) Create a scalar function that accepts the QuoteId, does the above check and returns 1 for pass, 0 for fail.
b) Add a check constraint on QuoteItem which calls that scalar function, passing the QuoteId and checking that it returns 0.
I realise using a function for a check constraint is bad for performance, as it operates row-by-row. It's a relatively low-activity system, so that might not be a problem, however I'd love to know if there is a better (ed: more performant / best practice) way of achieving the above?
ed2: The application does enforce this at data entry, but it's an important enough rule that a hard constraint on the db is desired as well. And. from what I've read, a trigger won't be as reliable as a constraint in this case(?)