On 10/19/05, Martijn Tonies <[EMAIL PROTECTED]> 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
