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"


Any feedback appreciated,
Isak

Isak Hansen wrote:

In SQL use a CHECK constraint:

.... CHECK
(NOT EXISTS
(SELECT 1
FROM tbl
GROUP BY foreign_key
HAVING SUM(amount) <> 0));

In case your DBMS doesn't support this it would help if you could state
what product and version you are using.

--
David Portas

David Portas wrote:
Sorry, should of course have mentioned that i use postgres 8.0.

Doesn't look like the above will work, i get the following error
message:
ERROR: cannot use subquery in check constraint


Thank you,
Isak

Isak Hansen wrote:


why not?

Yes. We actually do it with triggers. The child tables update the header
tablke. The constraint is on the header table.

Constraints on calculated columns are key to a complete treatment of
business rules in the server. A typical example would be customer credit
limit, where the sum of two aggregates (sum open invoices + sum open
orders) cannot exceed the user-entered credit limit.



--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)

Kenneth Downs wrote:
Was a bit concerned about putting too much business logic in the db,
but figure this is just 'data integrity'. Not like we're rewriting the
whole app as stored procedures or anything.

Think I'll pursue this further on the postgres mailing list, as I'll
need a ton of help getting anywhere.


Thank you,
Isak

Isak Hansen wrote:

Well don't get me started on where biz logic belongs

Seriously though, we put 100% of our biz logic in the db server, meaning
security, calculations, and constraints. We find it works well.

Good luck to you!

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)

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;

--CELKO-- wrote:
Looks interesting.

I'm going with the trigger appoach for now, though, as my ORM doesn't
play too well with views.


Regards,
Isak