sql constraints
Posted by:
b sollie
Date: March 04, 2005 09:18AM
can someone help my with the constraints in this script?
drop table medewerkers cascade constraints;
create table medewerkers
(mnr numeric(4) constraint M_PK primary key
constraint M_MNR_CHK check (mnr > 7000)
,naam char(12) constraint M_NAAM_NN not null
,voorl char(5) constraint M_VOORL_NN not null
,functie char(10)
,chef numeric(4) constraint M_CHEF_FK references medewerkers
,gbdatum DATE constraint M_GEBDAT_NN not null
,maandsal numeric(6,2) constraint M_MNDSAL_NN not null
,comm numeric(6,2)
,afd numeric(2) default 10
,constraint M_VERK_CHK check (decode(functie,'VERKOPER',0,1) +
decode(comm , NULL ,0,1) = 1)
);
REM ====================================================================
drop table afdelingen cascade constraints;
create table afdelingen
(anr numeric(2) constraint A_PK primary key
constraint A_ANR_CHK check ( mod(anr,10) = 0 )
,naam char(20) constraint A_NAAM_NN not null
constraint A_NAAM_UN unique
constraint A_NAAM_CHK check (naam = upper(naam) )
,locatie char(20) constraint A_LOC_NN not null
constraint A_LOC_CHK check (locatie = upper(locatie))
,hoofd numeric(4) constraint A_HOOFD_FK references medewerkers
);
REM ====================================================================
alter table medewerkers
add (constraint M_AFD_FK foreign key (afd) references afdelingen);
REM ====================================================================
drop table schalen cascade constraints;
create table schalen
(snr numeric(2) constraint S_PK primary key
,ondergrens numeric(6,2) constraint S_ONDER_NN not null
constraint S_ONDER_CHK check (ondergrens >= 0)
,bovengrens numeric(6,2) constraint S_BOVEN_NN not null
,toelage numeric(6,2) constraint S_TOELG_NN not null
,constraint S_OND_BOV check ( ondergrens <= bovengrens )
);
REM ====================================================================
drop table cursussen cascade constraints;
create table cursussen
(code char(4) constraint C_PK primary key
,omschrijving char(50) constraint C_OMSCHR_NN not null
,type CHAR(3) constraint C_TYPE_NN not null
,lengte numeric(2) constraint C_LENGTE_NN not null
,constraint C_CODE_CHK check (code = upper(code) )
,constraint C_TYPE_CHK check (type in ('ALG','BLD','DSG'))
);
REM ====================================================================
drop table uitvoeringen cascade constraints;
create table uitvoeringen
(cursus char(4) constraint U_CURSUS_NN not null
constraint U_CURSUS_FK references cursussen
,begindatum DATE constraint U_BEGIN_NN not null
,docent numeric(4) constraint U_DOCENT_FK references medewerkers
,locatie char(20)
,constraint U_PK primary key (cursus,begindatum)
);
REM ====================================================================
drop table inschrijvingen cascade constraints;
create table inschrijvingen
(cursist numeric(4) constraint I_CURSIST_NN not null
constraint I_CURSIST_FK references medewerkers
,cursus char(4) constraint I_CURSUS_NN not null
,begindatum DATE constraint I_BEGIN_NN not null
,evaluatie numeric(1) constraint I_EVAL_CHK
check (evaluatie in (1,2,3,4,5) )
,constraint I_PK primary key (cursist,cursus,begindatum)
,constraint I_UITV_FK foreign key (cursus,begindatum)
references uitvoeringen
);