Обсуждение: table inheritance and foreign key troubles

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

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

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

(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...

> 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