Re: Checking Multiplicity Constraints and Retrieving Data from Error Messages

On 20 Oct 2005, at 12:31, Martijn van Oosterhout wrote:
> 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.
>

I am not sure if CHECK constraints will work, as I don't think you
can reference another table in one of those. And I think it might
even not let you have a subquery (ie a select inside the check
statement). So I don't know whether I would be able to use CHECK for
that. Or am I being stupid and you actually can?

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

It wasn't a particular version. I haven't really used PostgreSQL yet,
I only set it up on my system yesterday. I do now have the latest
version for Macs, not exactly sure which one it is. But if it is true
that it does specify the row and character as you said, that's great
news.

Thanks for your help.
Ledina