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
