Re: General ISA and Foreign Key

BOUCHPAN-LERUST-JUERY Lionel wrote: >In SQL I have the following tables > >CREATE TABLE film( >VisaExploitation INTEGER
NOTNULL, >DureeTournage INTEGER NOT NULL, >Titre VARCHAR( 50 ), > >PRIMARY KEY ( VisaExploitation ) ); >CREATE TABLE
filmHistorique(>NbCostume INTEGER >) INHERITS ( film ); > >create table filmDocumentaire( > ) INHERITS ( film ); > >I
havea 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
haveto 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,
feedhim; if he thirst, give him drink;     for in so doing thou shalt heap coals of fire on his      head. Be not
overcomeof evil, but overcome evil with      good."      Romans 12:19-21