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

Both cross-named & compound foreign key constraints fail

From: "Steffen C(dot) Hulegaard" <shulegaa(at)txl(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Both cross-named & compound foreign key constraints fail
Date: 2000-11-27 19:44:35
Message-ID: 200011271944.LAA03555@gatekeeper.txl.com (view raw or flat)
Thread:
Lists: pgsql-bugs
/* $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(dot)owen(at)wgcr(dot)org>
 | URL         : http://www.postgresql.org/
 | Summary     : PostgreSQL client programs and libraries.
 | 
 | 11/27/2000  SC Hulegaard  Created and sent to pgsql-bugs(at)postgresql(dot)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 ;


pgsql-bugs by date

Next:From: pgsql-bugsDate: 2000-11-27 21:54:08
Subject: Both cross-named & compound foreign key constaints fail
Previous:From: Tom LaneDate: 2000-11-27 18:38:21
Subject: Re: PortalHeapMemoryFree...in diskless client

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