List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:October 19 2005 7:21pm
Subject:Re: Checking Multiplicity Constraints and Retrieving Details from Error Messages
View as plain text  
> >> 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
> 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)

(don't know if this syntax is exact, but you get the idea)

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

Thread
Checking Multiplicity Constraints and Retrieving Details from Error MessagesLedina Hido19 Oct
  • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesMartijn Tonies19 Oct
    • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesLedina Hido19 Oct
    • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesJochem van Dieten19 Oct
  • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesMartijn Tonies19 Oct
    • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesJochem van Dieten19 Oct
      • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesLedina Hido19 Oct
  • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesMartijn Tonies20 Oct
    • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesJochem van Dieten20 Oct
  • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesMartijn Tonies20 Oct
  • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesHarald Fuchs20 Oct
    • Re: Checking Multiplicity Constraints and Retrieving Details from Error MessagesJochem van Dieten20 Oct