Constraint on an aggregate?

[Isak Hansen]

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

[David Portas]

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

[Isak Hansen]

David Portas wrote:
> Isak Hansen wrote:
> > 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"
>

> 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.

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

[Kenneth Downs]

Isak Hansen wrote:

> 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)

[Isak Hansen]

Kenneth Downs wrote:
> Isak Hansen wrote:
>
> > 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.

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

[Kenneth Downs]

Isak Hansen 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.

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!

[--CELKO--]

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

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;

[Isak Hansen]

Looks interesting.

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

Regards,
Isak