Thread: 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