Received: from pinguin.gl.aser.de (pec-59-94.tnt4.b2.uunet.de [149.225.59.94]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8BAj6794304 for ; Tue, 11 Sep 2001 06:45:06 -0400 (EDT) (envelope-from gcg@gl.aser.de) Received: by pinguin.gl.aser.de (Postfix, from userid 1000) id 9400813098; Tue, 11 Sep 2001 12:49:15 +0200 (CEST) Content-Type: text/plain; charset="iso-8859-1" From: Christof Glaser Organization: gl.aser . software engineering . internet service To: Kevin Way , pgsql-sql@postgresql.org Subject: Re: table inheritance and foreign key troubles Date: Tue, 11 Sep 2001 12:49:13 +0200 X-Mailer: KMail [version 1.3.1] References: <20010911080452.A76783@bean.overtone.org> <20010911091250.B8DD213061@pinguin.gl.aser.de> In-Reply-To: <20010911091250.B8DD213061@pinguin.gl.aser.de> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <20010911104915.9400813098@pinguin.gl.aser.de> X-Archive-Number: 200109/52 X-Sequence-Number: 4698 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