Re: Checking Multiplicity Constraints and Retrieving Data from Error Messages

On Wed, Oct 19, 2005 at 03:25:25PM +0100, Ledina Hido 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.

You may be looking for CHECK constraints, although they are really just
a kind of trigger.

Note, there are two sides to such a trigger. You need a trigger on the
employees table to check that the limit is not exceeded during an
insert (presumably you don't need to check deletes). OTOH, you need a
trigger on the manager table so if someone changes the limit down, you
don't get caught out.

You can specify CHECK during table creation, but not triggers.

> Second, is there any way of getting more details out of an error
> message? So for example, when doing a bulk upload to the database,
> rather than just getting "Cannot add or update a child row: a foreign
> key constraint fails" I would like to know which particular insert
> statement (out of the 1000 I have) caused the problem, or which field
> in this statement broke the constraint.

What version? At least some recent versions specify the row that failed
and even the character, though I couldn't say when that was added...

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.