pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGeneral 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>
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 21:05 Oliver Elphick <olly@lfix.co.uk>
parent: BOUCHPAN-LERUST-JUERY Lionel <bouchpan@ie2.u-psud.fr>
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