Re: Both cross-named & compound foreign key constaints fail

On Mon, 27 Nov 2000 pgsql-bugs(at)postgresql(dot)org wrote:

Won't have a full answer until I'm home, but figured I'd send something.

> Description: Run the psql script below to generate
> the following error:
> psql:bug.sql:54: ERROR:
> constraint <unnamed>:
> table al_addresses_data does not have
> an attribute address_press_id
>
> Problem: The failing command is ALTER TABLE
> al_presses ADD CONSTRAINT ... FOREIGN KEY
> ... *REFERENCES* al_addresses_data
> (record_id, *press_id*).
> PostgreSQL\'s error message indicates that
> the DB is looking for an al_addresses_data
> attribute with the *same* name as the
> foreign key column in source table (i.e.
> al_presses.address_press_id). It *appears*
> that the REFERENCES list is being ignored
> (with respect to at least the second element
> of the REFERENCES list).
> Minor Problem: If the tables are left empty, the schema
> creates without error even though a latent
> error exists! Comment out the two insert
> statements to witness error free schema
> construction. This indicates that some
> DDL/schema-creation time validation is
> being deferred. It would be far more
> helpful to detect all schema errors
> during schema construction. A small point.
Okay, these two say to me that it's almost certainly not the ALTER
TABLE code that's generating the message, but instead the trigger itself
(the ALTER TABLE calls the trigger for each row already in the
table). Does it fail if you do an insert after getting a successful
create in the second case, or does it work there? I'm wondering if
I'm passing in bogus arguments to the trigger function in the data
check in certain cases.
7.1 should do column name checking at constraint creation
time, but I'm not sure if the other issue is fixed. I'll
check my 7.1 and 7.0 machine with sources when I get home. :)

> Minor Problem: The ALTER TABLE ... ADD CONSTRAINT command
> produces an error message about an <unknown>
> constraint. The failed constraint *DOES*
> have a given name. A small point - but a
> potential source of confusion.
This one should be fixed in 7.1, I miscopied something
from somewhere else and didn't get the constraint name to the
trigger.

> Aside: Trying to work around this bug with an
> al_addresses_data view that maps record_id
> to address_id and press_id to address_press_id
> also fails. This is a very minor feature/function
> issue once the direct al_addresses_data
> constraint works. Still, it would seem that
> a clever implementation of views might hide the
> table versus view distinction from the
> referential integrity logic ... and thus
> make this work by default. At present,
> this failing view work-around produces the
> following error when the ALTER TABLE is attempted:
> ERROR: system column oid not available -
> al_addresses is a view
> Of course, I'm way over my head on this point
> since I know nothing of PostgreSQL's internals ...

Unfortunately you can not currently constrain views that way.
The reason not much has been done about it is related to the
check constraints with subselects. You may need to actually constrain
all tables referenced by the view in order to make the constraint work
and that's a bigger problem, especially if the view has set value
functions, etc. (An insert to a table could cause say a count value
say to change which would orphan a row in the fk table)