Scrpit SQL de création de la base des vins restreinte

CREATE TABLE VINS 
(num integer PRIMARY KEY,
cru char (40) NOT NULL,
annee integer CONSTRAINT Cannee
	CHECK (annee between 1970 and 2000),
degre number(4,2) CONSTRAINT Cdegre
	CHECK (degre between 9.0 and 15.0)
);

CREATE TABLE PRODUCTEURS
(num integer ,
nom char(40),
prenom char(40),
region char(40),
PRIMARY KEY (num) );

ALTER TABLE PRODUCTEURS add CONSTRAINT Cregion
check (region in ('Bourgogne', 'Beaujolais', 'Alsace', 'Jura', 'Corse'));

ALTER TABLE PRODUCTEURS add CONSTRAINT CNom CHECK (nom IS NOT NULL);

CREATE TABLE RECOLTES
(nprod integer,
nvin integer,
quantite integer
);

ALTER TABLE RECOLTES add primary key (nprod, nvin);

ALTER TABLE RECOLTES add constraint refVIN foreign key (nvin)
references VINS(num) on delete cascade;

ALTER TABLE RECOLTES add constraint refREP foreign key (nprod) 
references PRODUCTEURS(num) on delete cascade;

Posté le 27 août 2009