From: Martijn Tonies
Date: October 20 2005 6:20am
Subject: Re: Checking Multiplicity Constraints and Retrieving Details from Error Messages
List-Archive: http://lists.mysql.com/mysql/190621
Message-Id: <00d801c5d53e$6749b6e0$c802a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: 7bit

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