List:General Discussion« Previous MessageNext Message »
From:Jochem van Dieten Date:October 19 2005 7:30pm
Subject:Re: Checking Multiplicity Constraints and Retrieving Details from Error Messages
View as plain text  
On 10/19/05, Martijn Tonies <m.tonies@stripped> wrote:
>
>>>> First of all, is there any way of limiting the number of rows in a
>>>> table, referencing to the same element of another table? For example,
>>>> force a manager not to have more than 10 employees under his control.
>>>> In a way this can be seen as checking the multiplicity of the
>>>> relation between the two tables. I know one way would be using
>>>> triggers, but I was wondering if there was a way of specifying this
>>>> when the table is constructed.

>>> The way to do this would be via CHECK constraints, but MySQL
>>> doesn't support them.
>>
>> CHECK constraints won't work. If I have a parent table and need to
>> maintain a multiplicity of 1 to 3 children in the child table, how is
>> a CHECK going to stop somebody from deleting all rows in the child
>> table?
>
> That depends on your check constraint implementation.
>
> Firebird, for example, allows you to reference other tables in SQL
> statement in your CHECK constraints.
>
> So, you could do:
>
> exists( select count(*) as cnt from mychildtable
> where parentid = mytable.parentid and cnt between 1 and 3)

That doesn't help: check constraints are evaluated only on insert and
update, not on delete. That's why you need an assertion.

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