Thread: Foreign Key woes -- 7.2 and ~7.3
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. -- 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.
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.")
Stephan Szabo <sszabo@megazone23.bigpanda.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
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@sss.pgh.pa.us>
To: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
Cc: "Rod Taylor" <rbt@zort.ca>; <pgsql-bugs@postgresql.org>; "Hackers
List" <pgsql-hackers@postgresql.org>
Sent: Tuesday, April 16, 2002 7:19 PM
Subject: Re: [BUGS] [HACKERS] Foreign Key woes -- 7.2 and ~7.3
> Stephan Szabo <sszabo@megazone23.bigpanda.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@postgresql.org so that your
> message can get through to the mailing list cleanly
>