pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtable inheritance and foreign key troubles
5+ messages / 3 participants
[nested] [flat]
* table inheritance and foreign key troubles
@ 2001-09-11 08:04 Kevin Way <kevin.way@overtone.org>
0 siblings, 1 reply; 5+ messages in thread
From: Kevin Way @ 2001-09-11 08:04 UTC (permalink / raw)
To: pgsql-sql
I'm having a little trouble with some inherited tables and a foreign key. Here's
a simplified case, to show the trouble.
CREATE TABLE node (
node_id SERIAL NOT NULL,
name TEXT NOT NULL,
PRIMARY KEY (node_id)
);
-- works just fine
CREATE TABLE users (
email TEXT NOT NULL
) INHERITS (node);
-- so far so good....
CREATE TABLE item (
reason TEXT NOT NULL,
author_id INT NOT NULL REFERENCES users (node_id)
) INHERITS (node);
ERROR: UNIQUE constraint matching given keys for referenced table "users" not found
Does this operation just require differing syntax, because the referenced field
is inherited from another table, or is this not possible?
Kevin Way
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: table inheritance and foreign key troubles
@ 2001-09-11 09:12 Christof Glaser <gcg@gl.aser.de>
parent: Kevin Way <kevin.way@overtone.org>
0 siblings, 2 replies; 5+ messages in thread
From: Christof Glaser @ 2001-09-11 09:12 UTC (permalink / raw)
To: Kevin Way <kevin.way@overtone.org>; pgsql-sql
On Tuesday, 11. September 2001 10:04, Kevin Way wrote:
> I'm having a little trouble with some inherited tables and a foreign
> key. Here's a simplified case, to show the trouble.
>
> CREATE TABLE node (
> node_id SERIAL NOT NULL,
> name TEXT NOT NULL,
> PRIMARY KEY (node_id)
> );
> -- works just fine
>
> CREATE TABLE users (
> email TEXT NOT NULL
> ) INHERITS (node);
> -- so far so good....
>
> CREATE TABLE item (
> reason TEXT NOT NULL,
> author_id INT NOT NULL REFERENCES users (node_id)
> ) INHERITS (node);
> ERROR: UNIQUE constraint matching given keys for referenced table
> "users" not found
That means, there is no UNIQUE constraing on users.node_id ;-)
Since users inherits that field from node, just make node.node_id
unique, or even a primary key.
> Does this operation just require differing syntax, because the
> referenced field is inherited from another table, or is this not
> possible?
In previous versions of Postgres it was possible to reference non-unique
fields. With 7.1.x this is, correctly, no longer possible. Inheritance
doesn't have anything do to with it in your case, though.
Hope that helps,
Christof
--
gl.aser . software engineering . internet service
http://gl.aser.de/ . Planckstraße 7 . D-39104 Magdeburg
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: table inheritance and foreign key troubles
@ 2001-09-11 10:49 Christof Glaser <gcg@gl.aser.de>
parent: Christof Glaser <gcg@gl.aser.de>
1 sibling, 1 reply; 5+ messages in thread
From: Christof Glaser @ 2001-09-11 10:49 UTC (permalink / raw)
To: Kevin Way <kevin.way@overtone.org>; pgsql-sql
On Tuesday, 11. September 2001 11:12, I wrote before I thought:
> On Tuesday, 11. September 2001 10:04, Kevin Way wrote:
> > I'm having a little trouble with some inherited tables and a foreign
> > key. Here's a simplified case, to show the trouble.
> >
> > CREATE TABLE node (
> > node_id SERIAL NOT NULL,
> > name TEXT NOT NULL,
Sorry, I missed that line:
> > PRIMARY KEY (node_id)
> > );
> > -- works just fine
> >
> > CREATE TABLE users (
> > email TEXT NOT NULL
> > ) INHERITS (node);
> > -- so far so good....
> >
> > CREATE TABLE item (
> > reason TEXT NOT NULL,
> > author_id INT NOT NULL REFERENCES users (node_id)
> > ) INHERITS (node);
> > ERROR: UNIQUE constraint matching given keys for referenced table
> > "users" not found
>
> That means, there is no UNIQUE constraing on users.node_id ;-)
> Since users inherits that field from node, just make node.node_id
> unique, or even a primary key.
PG cannot inherit primary keys or unique constraints, I recall now. So
you need to ensure uniqueness for users.node_id:
CREATE TABLE users (
node_id INT4 UNIQUE,
....
) INHERITS (node );
The constraints of users.node_id and node.node_id get merged magically.
This should work now on 7.1.3. I did test it this time.
Best regards,
Christof
--
gl.aser . software engineering . internet service
http://gl.aser.de/ . Planckstraße 7 . D-39104 Magdeburg
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: table inheritance and foreign key troubles
@ 2001-09-11 10:58 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Christof Glaser <gcg@gl.aser.de>
1 sibling, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2001-09-11 10:58 UTC (permalink / raw)
To: Kevin Way <kevin.way@overtone.org>; +Cc: pgsql-sql
(hopefully this doesn't double post... stupid mail system)
On Tue, 11 Sep 2001, Christof Glaser wrote:
> On Tuesday, 11. September 2001 10:04, Kevin Way wrote:
> > I'm having a little trouble with some inherited tables and a foreign
> > key. Here's a simplified case, to show the trouble.
> >
> > CREATE TABLE node (
> > node_id SERIAL NOT NULL,
> > name TEXT NOT NULL,
> > PRIMARY KEY (node_id)
> > );
> > -- works just fine
> >
> > CREATE TABLE users (
> > email TEXT NOT NULL
> > ) INHERITS (node);
> > -- so far so good....
> >
> > CREATE TABLE item (
> > reason TEXT NOT NULL,
> > author_id INT NOT NULL REFERENCES users (node_id)
> > ) INHERITS (node);
> > ERROR: UNIQUE constraint matching given keys for referenced table
> > "users" not found
>
> That means, there is no UNIQUE constraing on users.node_id ;-)
> Since users inherits that field from node, just make node.node_id
> unique, or even a primary key.
Actually node.node_id looks to be a pkey, but primary keys/unique don't
inherit, so users.node_id doesn't have the constraint. You'll need
a primary key(node_id) on users as well (note that this won't actually
enforce that values are unique across both node and users just within
each table. See past discussions about inheritance and foreign keys...
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: table inheritance and foreign key troubles
@ 2001-09-12 01:24 Kevin Way <kevin.way@overtone.org>
parent: Christof Glaser <gcg@gl.aser.de>
0 siblings, 0 replies; 5+ messages in thread
From: Kevin Way @ 2001-09-12 01:24 UTC (permalink / raw)
To: Christof Glaser <gcg@gl.aser.de>
> PG cannot inherit primary keys or unique constraints, I recall now. So
> you need to ensure uniqueness for users.node_id:
> CREATE TABLE users (
> node_id INT4 UNIQUE,
> ....
> ) INHERITS (node );
Thank you so much, this is where the flaw in my logic was hiding. Your
suggestion worked perfectly.
Kevin Way
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-09-12 01:24 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-09-11 08:04 table inheritance and foreign key troubles Kevin Way <kevin.way@overtone.org>
2001-09-11 09:12 ` Christof Glaser <gcg@gl.aser.de>
2001-09-11 10:49 ` Christof Glaser <gcg@gl.aser.de>
2001-09-12 01:24 ` Kevin Way <kevin.way@overtone.org>
2001-09-11 10:58 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox