Обсуждение: Checking Multiplicity Constraints and Retrieving Data from Error Messages

As part of my 4th Year Group Design Project, I am required to build a
database system that will validate and then store the data. As such I
am currently investigating different DB, to choose the most suitable
one. I liked many features of PostgreSQL (eg deferring transactions)
but there are a couple of things I am not very clear about that I
would need for the project.

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.

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.

Any help would be much appreciated.

Ledina

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

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

On Thu, Oct 20, 2005 at 12:44:53PM +0100, Ledina Hido wrote:
> 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?

Well, you'll need to make a function that does the work and specify
that function as the check constraint. It's true that directly you
can't specify other tables. PostgreSQL can see inside functions to see
what you are doing...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.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.

On Wed, 2005-10-19 at 15:25 +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.

That's a lack of function within the SQL standard and PostgreSQL doesn't
implement that as an extension.

XML Schema allows you to define minoccurs and maxoccurs, but not the
SQL:2003 standard.

It would be very cool if it did, I grant you. You have to do this
yourself via a trigger. Just make sure you create an index on the
columns and you'll be fine - apart from the extra cost of inserting
rows, so keep maxoccurs small.

Best Regards, Simon Riggs