pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
table 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>
  2001-09-11 09:12 ` Re: table inheritance and foreign key troubles Christof Glaser <gcg@gl.aser.de>
  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 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   ` Re: table inheritance and foreign key troubles Christof Glaser <gcg@gl.aser.de>
  2001-09-11 10:58   ` Re: table inheritance and foreign key troubles Stephan Szabo <sszabo@megazone23.bigpanda.com>
  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 08:04 table inheritance and foreign key troubles Kevin Way <kevin.way@overtone.org>
  2001-09-11 09:12 ` Re: table inheritance and foreign key troubles Christof Glaser <gcg@gl.aser.de>
@ 2001-09-11 10:49   ` Christof Glaser <gcg@gl.aser.de>
  2001-09-12 01:24     ` Re: table inheritance and foreign key troubles Kevin Way <kevin.way@overtone.org>
  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 08:04 table inheritance and foreign key troubles Kevin Way <kevin.way@overtone.org>
  2001-09-11 09:12 ` Re: table inheritance and foreign key troubles Christof Glaser <gcg@gl.aser.de>
  2001-09-11 10:49   ` Re: table inheritance and foreign key troubles Christof Glaser <gcg@gl.aser.de>
@ 2001-09-12 01:24     ` Kevin Way <kevin.way@overtone.org>
  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

* Re: table inheritance and foreign key troubles
  2001-09-11 08:04 table inheritance and foreign key troubles Kevin Way <kevin.way@overtone.org>
  2001-09-11 09:12 ` Re: table inheritance and foreign key troubles Christof Glaser <gcg@gl.aser.de>
@ 2001-09-11 10:58   ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  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


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