List:General Discussion« Previous MessageNext Message »
From:Jochem van Dieten Date:October 20 2005 8:42am
Subject:Re: Checking Multiplicity Constraints and Retrieving Details from Error Messages
View as plain text  
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
Thread
Checking Multiplicity Constraints and Retrieving Details from Error MessagesLedina Hido19 Oct
  • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesMartijn Tonies19 Oct
    • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesLedina Hido19 Oct
    • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesJochem van Dieten19 Oct
  • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesMartijn Tonies19 Oct
    • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesJochem van Dieten19 Oct
      • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesLedina Hido19 Oct
  • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesMartijn Tonies20 Oct
    • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesJochem van Dieten20 Oct
  • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesMartijn Tonies20 Oct
  • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesHarald Fuchs20 Oct
    • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesJochem van Dieten20 Oct