pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
General ISA and Foreign Key
3+ messages / 2 participants
[nested] [flat]

* General ISA and Foreign Key
@ 2001-05-07 18:46 BOUCHPAN-LERUST-JUERY Lionel <bouchpan@ie2.u-psud.fr>
  2001-05-07 21:05 ` Re: General ISA and Foreign Key Oliver Elphick <olly@lfix.co.uk>
  0 siblings, 1 reply; 3+ messages in thread

From: BOUCHPAN-LERUST-JUERY Lionel @ 2001-05-07 18:46 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: General ISA and Foreign Key
  2001-05-07 18:46 General ISA and Foreign Key BOUCHPAN-LERUST-JUERY Lionel <bouchpan@ie2.u-psud.fr>
@ 2001-05-07 21:05 ` Oliver Elphick <olly@lfix.co.uk>
  0 siblings, 0 replies; 3+ messages in thread

From: Oliver Elphick @ 2001-05-07 21:05 UTC (permalink / raw)
  To: BOUCHPAN-LERUST-JUERY Lionel <bouchpan@ie2.u-psud.fr>; +Cc: pgsql-sql

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 





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: General ISA and Foreign Key
@ 2001-05-09 12:25 BOUCHPAN-LERUST-JUERY Lionel <bouchpan@ie2.u-psud.fr>
  0 siblings, 0 replies; 3+ messages in thread

From: BOUCHPAN-LERUST-JUERY Lionel @ 2001-05-09 12:25 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-05-09 12:25 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-07 18:46 General ISA and Foreign Key BOUCHPAN-LERUST-JUERY Lionel <bouchpan@ie2.u-psud.fr>
2001-05-07 21:05 ` Oliver Elphick <olly@lfix.co.uk>
2001-05-09 12:25 Re: General ISA and Foreign Key BOUCHPAN-LERUST-JUERY Lionel <bouchpan@ie2.u-psud.fr>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox