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

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