Thread
-
Both cross-named & compound foreign key constraints fail
Steffen C. Hulegaard <shulegaa@txl.com> — 2000-11-27T19:44:35Z
/* $Id$ +-------------------------------------------------------------------- | No Copyright. Public Domain. +-------------------------------------------------------------------- | | bug.sql Cross named and compound foreign key constraint bug test | | Description: Run this script on an empty database to generate | the following error (reformatted into multiple lines): | 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). | 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. | 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 | source of confusion. | Problem: 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 clean 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 ... | Environment ---------------------------------------------------- | RedHat 6.2 | select version(); | PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 | # rpm -qi postgresql-7.0.2-2 | Name : postgresql Relocations: /usr | Version : 7.0.2 Vendor: The Ramifordistat | Release : 2 Build Date: Mon 12 Jun 2000 02:21:35 PM PDT | Install date: Fri 04 Aug 2000 11:40:39 AM PDT Build Host: utility.wgcr.org | Group : Applications/Databases Source RPM: postgresql-7.0.2-2.src.rpm | Size : 7431735 License: BSD | Packager : Lamar Owen <lamar.owen@wgcr.org> | URL : http://www.postgresql.org/ | Summary : PostgreSQL client programs and libraries. | | 11/27/2000 SC Hulegaard Created and sent to pgsql-bugs@postgresql.org + ------------------------------------------------------------------- */ CREATE TABLE al_descs ( name VARCHAR(84) NOT NULL, name_sort VARCHAR(84) NOT NULL, name_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, description VARCHAR(256) NOT NULL DEFAULT 'No description is available.', explanation TEXT NOT NULL DEFAULT 'No explanation is available.', priority INT4 NOT NULL DEFAULT 1, secondary BOOL NOT NULL DEFAULT TRUE ) ; /* A press is like a server farm/cluster */ CREATE TABLE al_presses ( record_id INT4 NOT NULL, address_id INT4 NOT NULL DEFAULT 3, address_press_id INT4 NOT NULL DEFAULT 3 ) INHERITS ( al_descs ) ; INSERT INTO al_presses (record_id, name, name_sort) VALUES (1, 'Foo', 'foo') ; /* Most entities have a compound internal/logical identifer ... The local server farm/cluster identifier and the server farm/cluster id */ CREATE TABLE al_ids ( record_id INT4 NOT NULL, press_id INT4 NOT NULL DEFAULT 1, CONSTRAINT al_ids_presses_fk FOREIGN KEY ( press_id ) REFERENCES al_presses ( record_id ) MATCH FULL ON DELETE RESTRICT ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED ) ; CREATE TABLE al_addresses_data ( fictional BOOL NOT NULL DEFAULT FALSE, verified BOOL NOT NULL DEFAULT FALSE, street_number VARCHAR(16) NOT NULL DEFAULT '', street_directional VARCHAR(2) NOT NULL DEFAULT '', street_name VARCHAR(32) NOT NULL DEFAULT '', street_suffix VARCHAR(12) NOT NULL DEFAULT '' ) INHERITS ( al_ids ) ; INSERT INTO al_addresses_data (record_id, press_id) VALUES ( 3, 3 ) ; ALTER TABLE al_presses ADD CONSTRAINT al_presses_address_data_fk FOREIGN KEY (address_id, address_press_id) REFERENCES al_addresses_data (record_id, press_id) MATCH FULL ON DELETE RESTRICT ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED ; DROP TABLE al_addresses_data ; DROP TABLE al_presses ; DROP TABLE al_ids ; DROP TABLE al_descs ;