rZbNYYJd

1drop table if exists client cascade;
2drop table if exists article cascade;
3drop table if exists commande cascade;
4drop table if exists ligne_commande cascade;
5
6CREATE TABLE client(
7	numclient serial,
8	nom varchar(30) not null,
9	prenom varchar(30) not null,
10	telephone char(10),
11	email varchar(100),
12
13	constraint pk_client_num_client primary key (numclient),
14	constraint ck_client_email check (email like '%@%.%'),
15	constraint uq_client unique (nom, prenom, telephone));
16
17CREATE TABLE article(
18	codearticle char(5),
19	nom varchar(50),
20	prix numeric(5,2),
21	prix_promotion numeric(5,2),
22
23	constraint pk_article_codearticle primary key (codearticle),
24	constraint ck_article_prix check (prix between 0 and 500),
25	constraint ck_article_prixpromo check (prix_promotion between 0 and prix),
26	constraint ck_article_codearticle check (codearticle like '_____')); 
27
28CREATE TABLE commande(
29	numcommande serial,
30	numclient integer,
31	dateC date,
32
33	constraint pk_commande_numcommande primary key (numcommande));
34
35
36create table ligne_commande (
37	numcommande integer,
38	numligne integer,
39	codearticle char(5),
40	quantite integer,
41	
42	constraint pk_lignecommande_ncnl primary key (numcommande,numligne),
43	constraint ck_lignecommande_quantite check(quantite>0));
44	
45ALTER TABLE commande ADD constraint fk_commande_numclient foreign key (numclient) references client(numclient);
46
47ALTER TABLE ligne_commande 
48ADD constraint ujèyuuyjhn foreign key (numcommande) references commande(numcommande),
49ADD constraint pmlp foreign key (codearticle) references article(codearticle);