Re: I have a prolble with a foreign key

In article <mknQ5.3135$Am5.171767_at_wagner.videotron.net>,   "La famille Labrie" <psautier_at_videotron.ca> wrote:
> Can you help me with a script please?
>
> These is the script:
> clear screen
> --Supprimer toutes les tables existantes
> drop table auteur cascade constraints;
> drop table editeur cascade constraints;
> drop table client cascade constraints;
> drop table livre cascade constraints;
> drop table commande cascade constraints;
>
> --Supprimer toutes les séquences existantes
> drop sequence auteur_seq;
> drop sequence editeur_seq;
> drop sequence client_seq;
> drop sequence livre_seq;
>
> --Création de la table auteur
> create table auteur( numaut number(4) constraint pk_auteur_numaut
 primary
> key,
> nomaut varchar2(30) not null,
> preaut varchar2(30) not null
> );
>
> --Création de la table editeur
> create table editeur( numedi number(4) constraint pk_editeur_numedi
 primary
> key,
> nomedi varchar2(30) not null,
> adredi varchar2(60) not null
> );
>
> --Création de la table client
> create table client( numcli number(4) constraint pk_client_numcli
 primary
> key,
> nomcli varchar2(30) not null,
> precli varchar2(30) not null
> );
>
> --Création de la table livre
> create table livre( numliv number(6),
> titliv varchar2(60) not null,
> numaut number(4) constraint fk_livre_numaut
 references
> auteur(numaut),
> numedi number(4) constraint fk_livre_numedi
 references
> editeur(numedi),
> datedi date not null,
> constraint pk_livre_cles primary key(numliv,numaut,numedi)
> );
>
> --Création de la table commande
> create table commande( numcom number(4),
> numliv number(6) constraint fk_livre_numliv
> references livre(numliv),
> numcli number(4) constraint fk_client_numcli
> references client(numcli),
> qtecom number(2) not null,
> constraint pk_commande_cles primary
> key(numcom,numliv,numcli)
> );
>
> The conditions are:
>
> 1- One commande(order) has one or more livre(book)
> 2- one commande(order) has onely one client
> 3- one livre(book) has only one auteur(author)
> 4- one livre (book) has only one editeur(editor)
>
> Thank
>
>

1 I would make sure to use a meaningless sequence number as surrogate primary key for all tables (maybe with the exception of livre: if you could use the ISBN for that: it's unique) 2 Doing so you definitely shouldn't use *composite* primary keys on lower levels (livre), if you don't have the ISBN, numliv should be a sufficient primary key.
3 this means in the table commende the fk definition is correct, the pk definition for livre isn't (and a fk always should reference *all* columns of a pk). Doing so constraints 1 and 2 are adressed. 4 to addres your constraint 3 you need a composite *unique key* on numliv and numaut
5 and a composite unique key on numliv, numedi for constraint 4

As you already seem to have the syntax correct, and I currently don't have much time, I assume you can change the create table statements yourself.

Hth,

--
Sybrand Bakker, Oracle DBA

All standard disclaimers apply
------------------------------------------------------------------------


Sent via Deja.com http://www.deja.com/
Before you buy.