Skip site navigation (1) Skip section navigation (2)

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

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Rod Taylor <rbt(at)zort(dot)ca>
Cc: <pgsql-bugs(at)postgresql(dot)org>,Hackers List <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] Foreign Key woes -- 7.2 and ~7.3
Date: 2002-04-16 17:18:52
Message-ID: 20020416101434.W48544-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
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.")



In response to

Responses

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2002-04-16 17:24:12
Subject: Re: Scanner performance (was Re: 7.3 schedule)
Previous:From: Bruce MomjianDate: 2002-04-16 17:06:48
Subject: Re: Places to update when a new version is out

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2002-04-16 19:26:10
Subject: Re: replace_vars_with_subplan_refs
Previous:From: Rod TaylorDate: 2002-04-16 16:10:56
Subject: Foreign Key woes -- 7.2 and ~7.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group