List:General Discussion« Previous MessageNext Message »
From:Ledina Hido Date:October 19 2005 9:22pm
Subject:Re: Checking Multiplicity Constraints and Retrieving Details from Error Messages
View as plain text  
On 19 Oct 2005, at 20:30, Jochem van Dieten wrote:

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

I am not sure you could use that. To start with I don't think CHECK  
supports subqueries, but even if it did I would need to specify that  
the items in the table referencing the same parent as the one to be  
inserted are between 1 and 3. I am not sure you can specify that with  
a query similar to the above. "parentid = mytable.parentid" I don't  
think would work as you are specifying another column as supposed to  
a value. I really cannot think of a way to specify that constraint  
using check statement. Or am I being really stupid and missing some  
crucial point here?

Many thanks,
Ledina
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