> >> 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.
> In MySQL triggers are the only way. In SQL you might be able to use an
> assertion depending on your exact needs. (Don't know if there is any
> database that actually implements them per the standard.)
> > 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)
(don't know if this syntax is exact, but you get the idea)
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Database development questions? Check the forum!