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