> >>>> 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.
Hmmm, would that be SQL standard? Or implementation specific? >From a logical point of view, I don't see why a "check constraint" shouldn't be checking on all operations. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
