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

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> On Tue, 16 Apr 2002, Rod Taylor wrote:
>> You'll notice there isn't a primary key at all -- which shouldn't be
>> an issue as there is still the unique.

> 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.")

Not sure if Rod got the point here, but: you *can* reference a column
that's only UNIQUE and not PRIMARY KEY. You just have to name it
explicitly, eg.

regression=# create table stuff2 (stuff_id int4 references stuff(stuff_id)
regression(# on update cascade on delete cascade);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE

This is all per-spec, AFAIK.

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

That would be contrary to spec, and I see no need for it...

regards, tom lane