On 19 Oct 2005, at 20:30, Jochem van Dieten wrote:
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)
I am not sure you could use that. To start with I don't think CHECK
supports subqueries, but even if it did I would need to specify that
the items in the table referencing the same parent as the one to be
inserted are between 1 and 3. I am not sure you can specify that with
a query similar to the above. "parentid = mytable.parentid" I don't
think would work as you are specifying another column as supposed to
a value. I really cannot think of a way to specify that constraint
using check statement. Or am I being really stupid and missing some
crucial point here?
Many thanks,
Ledina
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
