Received: from gl.aser.de (gl.aser.de [213.252.128.30]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2MH9Zu28378 for ; Thu, 22 Mar 2001 12:09:35 -0500 (EST) (envelope-from gcg@gl.aser.de) Received: from pinguin (gcg@node100.dial.Uni-Magdeburg.DE [141.44.160.100]) by gl.aser.de (8.11.2/8.11.2/Debian 8.11.2-1) with SMTP id f2MHFKa15831; Thu, 22 Mar 2001 18:15:20 +0100 Content-Type: text/plain; charset="iso-8859-1" From: Christof Glaser Organization: gl.aser software engineering . internet service To: Johannes =?iso-8859-1?q?Gr=F8dem?= , pgsql-sql@postgresql.org Subject: Re: Foreign key referencing subclasses. Date: Thu, 22 Mar 2001 18:16:47 +0100 X-Mailer: KMail [version 1.2] References: <20010322151847.A26189@unity.copyleft.no> In-Reply-To: <20010322151847.A26189@unity.copyleft.no> MIME-Version: 1.0 Message-Id: <01032218164700.00782@pinguin> Content-Transfer-Encoding: 8bit X-Archive-Number: 200103/450 X-Sequence-Number: 2118 Hi Johannes, On Thursday, 22. March 2001 15:18, Johannes Grødem wrote: > Hi, > > it seems I can't have a foreign key that references some subclass. > Postgres says it can't figure out what its primary key is. The > primary key is defined in the superclass. > > I have something like this: > > CREATE TABLE resource_record( > rrid SERIAL > -- etc. > ); There is no primary key for this table. Just write PRIMARY KEY after SERIAL. > CREATE TABLE soa_record( > -- blah, blah > ) INHERITS(resource_record); > > CREATE TABLE domain( > -- ... > soaid REFERENCES soa_record -- * > ); > > > * = This doesn't work. I can reference resource_record here, but I > can not reference soa_record that way. Referencing resource_record* > doesn't work either. Primary keys as well as other indexes are not inherited, unfortunately. You should create the indexes for any subclass manually, like: CREATE INDEX soa_record_pkey ON soa_record ( rrid ); You could also state the referenced field name (you actually have to, if you happen to reference to a non-primary key field): CREATE TABLE ... soaid int4 REFERENCES soa_record ( rrid ) ^^^^ Note that SERIAL is actually int4 with automagically created sequences. You can't leave out the column's type for references. And note also that referencing to a superclass* (including subclasses) does not work for 7.0.x. I think it changes with 7.1 but I'm not sure. You could create your own triggers, though. Hope that helps, Christof. -- gl.aser . software engineering . internet service http://gl.aser.de/ . Planckstraße 7 . D-39104 Magdeburg Tel. +49.391.7 44 77 10 . Fax +49.391.7 44 77 13 . Mobil 0177.77 92 84 3