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

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.