Thread: General ISA and Foreign Key

I have a problem concerning an University assignment
in SQL. I am running PostgreSQL. Below part of the E/R diagram
in ASCII art.



( #VisaExploitation )     |--------          //\\        --------
|    |        //  \\        |    |
|  1    |<------------//     \\---------| copy    |
|    |             \\ from//        |    |
--------               \\  //        --------  |                \\//           |      |                       |  /\
                 (#copy ) /ISA\ ------
 
|    |
|    |
----   ----
|   |  |   |
| 2 |  | 3 |
-----  -----

1: Film
2: SpecialFilm
3: HistoricalFilm 


2 and 3 inherits from film :
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.

Thanking you in Advance,
Lionel

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 
 

I thank you very much for your help.
Regards,
Lionel