On 20 Oct 2005 10:13:56 +0200, Harald Fuchs wrote:
> Jochem van Dieten writes:
>> Back in reality you don't enforce this using DDL. Apart from the fact
>> that I wouldn't know a single database that implements ASSERTIONs
>> according to the SQL standard, can you imagine having to run some
>> SELECT fk FROM table GROUP BY fk HAVING COUNT(fk) NOT BETWEEN x AND y
>> on every commit? This is something you enforce with triggers or
>> circumvent by using a stored procedure for all DML operations.
> If some multiplicity is one of your business rules, it doesn't matter
> if you put it into a trigger, a stored procedure, or an assertion -
> you'll have to run the query above on every commit anyway.
If you put it in a trigger or a stored procedure you can reduce it to:
SELECT COUNT(fk) FROM table WHERE fk = NEW.fk GROUP BY fk
SELECT COUNT(fk) FROM table WHERE fk = OLD.fk GROUP BY fk
Unles you are bulk loading this performs much better.