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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
