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@xxxxxxxxx> 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.
Attachment:
pgpU4Brt7v7rm.pgp
Description: PGP signature
