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

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

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: 9sch1(at)txl(dot)com, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Both cross-named & compound foreign key constaints fail
Date: 2000-11-30 15:56:17
Message-ID: Pine.BSF.4.21.0011300747560.43634-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugs

Okay.  On current sources, this seems to work with only a few changes.
You need unique or primary key constraints on the columns being
referenced (this is part of the spec but was not checked in 7.0)

A couple of other things, currently constraints don't inherit very well.
So, you'd probably want to have the fk constraint on al_ids on
al_addresses_data as well and the unique constraints need to be on the
targets of the fk constraints explicitly.

> 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
> 


In response to

pgsql-bugs by date

Next:From: mohamed hgugDate: 2000-11-30 16:15:09
Subject: a probleme with postgresql
Previous:From: Gena GurchonokDate: 2000-11-30 12:50:41
Subject: query execution time

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