Thread: Changes for Foreign Keys from 7.0.3 to 7.1beta?

Hello,
the following script is a simplified excerpt from a database for a
content-management-system:
--- snip ---
\connect template1 postgres
drop database test;
create database test;
\connect test postgres

create table LNG (
   LNG_ID               char(2),
   LNG_NAME             varchar(30),
   primary key (LNG_ID)
)
;

create table OBJ (
   OBJ_ID               integer     not null,
   OBJ_LNG_ID           char(2) constraint FK_OBJ_LNG_ID
                            references LNG(LNG_ID),
   OBJ_CONT             text,
   primary key (OBJ_ID,OBJ_LNG_ID)
)
;

create table RSC (
   RSC_ID               serial,
   RSC_OBJ_ID           integer constraint FK_RSC_OBJ_ID
                               references OBJ(OBJ_ID)
                          on update cascade
                          on delete cascade,
   primary key (RSC_ID)
)
;
--- snap ---

Resources for a page are collected in table RSC, language-dependancy is
resolved by combining OBJ_LNG_ID and OBJ_ID.
Now run in 7.0.3 (RedHat 7.0-RPMs) this script will do without problems,
however with 7.1beta (CVS updated on 12-28-2000) I get:
ERROR:  UNIQUE constraint matching given keys for referenced table "obj" not
found
Do I have to have a UNIQUE index for OBJ_ID then? Well, I do not see the
point why this has to be.

Any hints?

Best Regards
Mirko

On Fri, 29 Dec 2000, Mirko Zeibig wrote:

> Resources for a page are collected in table RSC, language-dependancy is
> resolved by combining OBJ_LNG_ID and OBJ_ID.
> Now run in 7.0.3 (RedHat 7.0-RPMs) this script will do without problems,
> however with 7.1beta (CVS updated on 12-28-2000) I get:
> ERROR:  UNIQUE constraint matching given keys for referenced table "obj" not
> found
> Do I have to have a UNIQUE index for OBJ_ID then? Well, I do not see the
> point why this has to be.

Yes, because the spec requires it.  The reason is that the foreign key
constraint as defined by SQL is not well defined in the case where there
is a lack of uniqueness on the referenced table except possibly in the
case of match partial.
We hadn't gotten around to checking in 7.0 which should have been in
the documentation for create table in the section on the references
constraint (or at least it's in the source for that manpage).

-------------
SQL92 11.8 Syntax Rules 2a:
        a) If the <referenced table and columns> specifies a <reference
          column list>, then the set of column names of that <refer-
          ence column list> shall be equal to the set of column names
          in the unique columns of a unique constraint of the refer-
          enced table. Let referenced columns be the column or columns
          identified by that <reference column list> and let refer-
          enced column be one such column. Each referenced column shall
          identify a column of the referenced table and the same column
          shall not be identified more than once.