Skip site navigation (1) Skip section navigation (2)

Both cross-named & compound foreign key constaints fail

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Both cross-named & compound foreign key constaints fail
Date: 2000-11-27 21:54:08
Message-ID: 200011272154.eARLs8A99229@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Steffen Hulegaard (9sch1(at)txl(dot)com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Both cross-named & compound foreign key constaints fail

Long Description
	This bug report is a near clone of one emailed in.  I just 
discovered this web interface for bug report submission, so I am 
also using it to ensure that it is logged properly.

Thanks, Steffen      (P.S.  PostgreSQL is looking very nice!
                            Keep up the awesome work! )

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.
  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.
          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 ... 
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: TheRamifordistat
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(dot)owen(at)wgcr(dot)org>
URL         : http://www.postgresql.org/
Summary     : PostgreSQL client programs and libraries.



Sample Code
  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 ;



No file was uploaded with this report


Responses

pgsql-bugs by date

Next:From: Rainer MagerDate: 2000-11-27 22:28:03
Subject: Postgres 7 pgdump problems with views
Previous:From: Steffen C. HulegaardDate: 2000-11-27 19:44:35
Subject: Both cross-named & compound foreign key constraints fail

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group