Any feedback appreciated,
Isak
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
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
> Probably not a great idea, but is it possible to create a constraint on
^^^^^^^^^^^^^
why not?
> an aggregate of rows? E.g. "sum(amount) = 0 group by foreign_key"
>
Yes. We actually do it with triggers. The child tables update the header
tablke. The constraint is on the header table.
>
> Any feedback appreciated,
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)
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
>
> 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.
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!
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;
Looks interesting.
I'm going with the trigger appoach for now, though, as my ORM doesn't
play too well with views.
Regards,
Isak