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

Understood. It's not what I was expecting to happen.

Normally I always specifically state the match, so I was a little
surprised by the behaviour.

Makes sense to match the primary key and only the primary key though.

--
Rod Taylor

Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. You cannot imagine why you ever felt
otherwise.

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Stephan Szabo" <sszabo(at)megazone23(dot)bigpanda(dot)com>
Cc: "Rod Taylor" <rbt(at)zort(dot)ca>; <pgsql-bugs(at)postgresql(dot)org>; "Hackers
List" <pgsql-hackers(at)postgresql(dot)org>
Sent: Tuesday, April 16, 2002 7:19 PM
Subject: Re: [BUGS] [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
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>