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