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

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
>