Contrainte venant d'une autre table

drop table Jeux cascade constraint;
drop table Personnes cascade constraint;
drop table Epreuves cascade constraint;
drop table Equipes cascade constraint;
drop table Apartenir cascade constraint;
drop table FairePartie cascade constraint;
drop table Arbitrer cascade constraint;
drop table Entrainer cascade constraint;
drop table SinscrireEqp cascade constraint;
drop table SinscrirePers cascade constraint;


create table jeux(
    DateJeux number(4),
    VilleJeux varchar2(25),   
    SaisonJeux varchar2(5),
    constraint pk_jeux primary key (DateJeux),
    constraint ck1_jeux check (SaisonJeux in ('ete','hiver')),
    constraint ck2_jeux check ((saisonJeux='ete' and mod(DateJeux,4)=2016) or (SaisonJeux='hiver' and mod(DateJeux,4)=2018))
);

create table personnes(
    NomPers varchar2(25),
    PrenomPers varchar2(25),
    DateNaissPers date,
    SexePers char(1),
    NationPers char(3),
    constraint pk_pers primary key (NomPers, PrenomPers),
    constraint ck1_pers check (SexePers in ('M','F'))
);

create table epreuves(
    NomEpr varchar2(25),
    TypeEpr varchar2(12),
    SaisonEpr varchar2(5),
    constraint pk_epr primary key (NomEpr),
    constraint ck1_epr check (TypeEpr in ('individuelle','equipe')),
    constraint ck2_epr check (SaisonEpr in ('ete','hiver'))
);

create table equipes(
    NomEqp varchar2(25),
    TailleMinEqp number,
    SportEqp varchar2(25),
    NationEqp char(3),
    constraint pk_eqp primary key (NomEqp),
    constraint ck1_eqp check (TailleMinEqp>=2)
);

create table apartenir(
    EpreuveApart varchar2(25),
    JeuxApart number(4),
    constraint fk1_apart foreign key (EpreuveApart) references epreuves (NomEpr),
    constraint fk2_apart foreign key (JeuxApart) references jeux (DateJeux)
);

create table FairePartie(
    NomPersFaire varchar2(25),
    PrenomPersFaire varchar2(25),
    EquipeFaire varchar2(25),
    NationFaire char(3),
    constraint fk1_faire foreign key (NomPersFaire, PrenomPersFaire) references Personnes (NomPers, PrenomPers),
    constraint fk2_faire foreign key (EquipeFaire) references Equipes (NomEqp)
/*,
    constraint ck1_faire check(exist([/contents/1068-sql-commande-select select] NationFaire from Personnes where NomPers=NomPersFaire))


*/
);

create table Arbitrer(
    JeuxArbi number(4),
    EpreuveArbi varchar2(25),
    NomPersArbi varchar2(25),
    PrenomPersArbi varchar2(25),
    RangArbi number(2),
    NomSupArbi varchar2(25),
    PrenomSupArbi varchar2(25),
    constraint fk1_arbi foreign key (JeuxArbi) references Jeux (DateJeux),
    constraint fk2_arbi foreign key (EpreuveArbi) references Epreuves (NomEpr),
    constraint fk3_arbi foreign key (NomPersArbi, PrenomPersArbi) references Personnes (NomPers, PrenomPers),
    constraint fk4_arbi foreign key (NomSupArbi, PrenomSupArbi) references Personnes (NomPers, PrenomPers),
    constraint ck1_arbi check (RangArbi in ('chef','video','fraude'))
);

create table Entrainer(
    EpreuveEntr varchar2(25),
    NomPersEntr varchar2(25),
    PrenomPersEntr varchar2(25),
    JeuxEntr number(4),
    constraint fk1_entr foreign key (EpreuveEntr) references Epreuves (NomEpr),
    constraint fk2_entr foreign key (NomPersEntr, PrenomPersEntr) references Personnes (NomPers, PrenomPers),
    constraint fk3_entr foreign key (JeuxEntr) references Jeux (DateJeux)
);

create table SinscrireEqp(
    EquipeSinsEqp varchar2(25),
    EpreuveSinsEqp varchar2(25),
    JeuxSinsEqp number(4),
    RangSinsEqp number(2),
    constraint fk1_sinsEqp foreign key (EquipeSinsEqp) references Equipes (NomEqp),
    constraint fk2_sinsEqp foreign key (EpreuveSinsEqp) references Epreuves (NomEpr),
    constraint fk3_sinsEqp foreign key (JeuxSinsEqp) references Jeux (DateJeux)
/*,
    constraint ck1_sinsEqp check (count(exist(select NomPersFaire, PrenomPersFaire
                                              from FairePartie where EquipeFaire=EquipeSinsEqp))
                                 >=(select TailleMinEqp from Equipes where NomEqp=EquipeSinsEqp)),
    constraint ck2_sinsEqp check (exist(select NomPersEntr, PrenomPersEntr from Entrainer
                                        where (select NAtionPers from Personnes
                                               where NomPers=NomPersEntr and PrenomPers=PrenomPersEntr)
                                        =(select NationEqp from Equipes where NomEqp=EquipeSinsEqp))),
    constraint ck3_sinsEqp check(exist(select RangArbi from Arbitrer
                                       where JeuxArbi=JeuxSinsEqp and EpreuveArbi=EpreuveSinsEqp and RangArbi='chef')),
    constraint ck4_sinsEqp check(exist(select TypeEpr from Epreuve where TypeEpr='Equipe' and NomEpr=EpreuveSinsEqp))


*/
);

create table SinscrirePers(
    NomPersSinsPers varchar2(25),
    PrenomPersSinsPers varchar2(25),
    EpreuveSinsPers varchar2(25),
    JeuxSinsPers number(4),
    RangSinsPers number(2),
    constraint fk1_sinsPers foreign key (NomPersSinsPers, PrenomPersSinsPers) references Personnes (NomPers, PrenomPers),
    constraint fk2_sinsPers foreign key (EpreuveSinsPers) references Epreuves (NomEpr),
    constraint fk3_sinsPers foreign key (JeuxSinsPers) references Jeux (DateJeux)
/*,
    constraint ck1_sinsPErs check(exist(select NomPersEntr, PrenomPersEntr from Entrainer
                                       where (select NationPers from Personnes
                                             where NomPers=NomPersEntr and PrenomPers=PrenomPersEntr)
                                       =(select NationPers from Personnes
                                        where NomPers=NomPersSinsPErs and PrenomPers=PrenomPersSinsPers))),
    constraint ck2_sinsPers check(exist(select RangArbi from Arbitrer
                                       where JeuxArbi=JeuxSinsPers and EpreuveArbi=EpreuveSinsPers and RangArbi='chef')),
    constraitn ck3_sinsPers check(exist(select TypeEpr from Epreuve where TypeEpr='individuelle' and NomEpr=EpreuveSinsPers))


*/
);