Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f47MkjA26119 for ; Mon, 7 May 2001 18:46:45 -0400 (EDT) (envelope-from olly@lfix.co.uk) Received: from lfix.demon.co.uk ([158.152.59.127] helo=linda.lfix.co.uk) by anchor-post-31.mail.demon.net with esmtp (Exim 2.12 #1) id 14wtm5-000NSq-0V; Mon, 7 May 2001 23:46:42 +0100 Received: from lfix.co.uk (olly@localhost [127.0.0.1]) by localhost (8.12.0.Beta7/8.12.0.Beta7/Debian 8.12.0.Beta7-1) with ESMTP id f47L5ciW014707; Mon, 7 May 2001 22:06:32 +0100 Message-Id: <200105072106.f47L5ciW014707@linda.lfix.co.uk> X-Mailer: exmh version 2.3.1 01/18/2001 (debian 2.3.1-1) with nmh-1.0.4+dev X-URL: http://www.lfix.co.uk/oliver X-face: "xUFVDj+ZJtL_IbURmI}!~xAyPC"Mrk=MkAm&tPQnNq(FWxv49R}\>0oI8VM?O2VY+N7@F- KMLl*!h}B)u@TW|B}6 cc: pgsql-sql@postgresql.org Subject: Re: General ISA and Foreign Key In-reply-to: Message from BOUCHPAN-LERUST-JUERY Lionel of Mon, 07 May 2001 20:46:32 +0200. <20010507204632.A24888@pc48.ie2.u-psud.fr> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Date: Mon, 07 May 2001 22:05:18 +0100 From: "Oliver Elphick" X-Archive-Number: 200105/53 X-Sequence-Number: 2854 BOUCHPAN-LERUST-JUERY Lionel wrote: >In SQL I have the following tables > >CREATE TABLE film( >VisaExploitation INTEGER NOT NULL, >DureeTournage INTEGER NOT NULL, >Titre VARCHAR( 50 ), > >PRIMARY KEY ( VisaExploitation ) ); >CREATE TABLE filmHistorique( >NbCostume INTEGER >) INHERITS ( film ); > >create table filmDocumentaire( > ) INHERITS ( film ); > >I have a weak entity: > >CREATE TABLE copie( >NumCopie INTEGER NOT NULL, >VisaExploitation INTEGER NOT NULL, >PRIMARY KEY( VisaExploitation, NumCopie ), >FOREIGN KEY( VisaExploitation ) REFERENCES film ON DELETE CASCADE ); > >The problem is I have to be able to have the constraint on >both 2 and 3 and I can't figure how to implement this. You can't do this at present, because there is no support for a foreign key constraint on an inheritance hierarchy (it is a major defect with the current implementation of inheritance). Although inheritance is conceptually correct, the lack of implementation suggests an alternative course: "film" should contain a row for every film and "filmHistorique" and "filmDocumentaire" should have foreign key constraints on "film". You could maintain the contents of "film" by triggers on the other two tables. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Dearly beloved, avenge not yourselves, but rather give place unto wrath. For it is written, Vengeance is mine; I will repay, saith the Lord. Therefore if thine enemy hunger, feed him; if he thirst, give him drink; for in so doing thou shalt heap coals of fire on his head. Be not overcome of evil, but overcome evil with good." Romans 12:19-21