Re: [HACKERS] Foreign Key woes -- 7.2 and ~7.3

On Tue, 16 Apr 2002, Rod Taylor wrote:

> b=# create table stuff (stuff_id serial unique);
> NOTICE: CREATE TABLE will create implicit sequence
> 'stuff_stuff_id_seq' for SERIAL column 'stuff.stuff_id'
> NOTICE: CREATE TABLE / UNIQUE will create implicit index
> 'stuff_stuff_id_key' for table 'stuff'
> CREATE
> b=# create table stuff2 (stuff_id int4 references stuff on update
> cascade on delete cascade);
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ERROR: PRIMARY KEY for referenced table "stuff" not found
>
>
> You'll notice there isn't a primary key at all -- which shouldn't be
> an issue as there is still the unique.
>
> Not the brightest thing to do, but surely the primary key shouldn't be
> enforced to exist before a plain old unique.
>
> If thats the case, then unique indecies need to be blocked until there
> is a primary key, or the first one should be automatically marked as
> the primary key.

If you're not specifying the columns in the references constraint, it
means specifically referencing the primary key of the table. If there
is no primary key, it's an error ("If the <referenced table and columns>
does not specify a <reference column list>, then the table descriptor
of the referenced table shall include a unique constraint that specifies
PRIMARY KEY.")