-- Création des tables et contraintes en SQL

drop table dansrecette;
drop table recette;
drop table ingredient;
drop table categorie;
drop table unite;



Create table categorie(
Idcategorie int primary key,
Nomcategorie varchar(50)
);

Create table unite(
Idunite int primary key,
Nomunite varchar(20)
);


Create table recette(
Idrecette int primary key,
Nomrecette varchar(50),
Nombreparts int,
Idcategorie int,
Constraint fk_categorie foreign key (idcategorie) references categorie(idcategorie)
);

Create table ingredient(
Idingredient int primary key,
Nomingredient varchar(50),
Idunite int,
Constraint fk_unite foreign key (idunite) references unite(idunite)
);

Create table dansrecette(
Idrecette int,
Idingredient int,
Quantité int,
Constraint pk primary key (idrecette, idingredient),
Constraint fk_recette foreign key (idrecette) references recette(idrecette),
Constraint fk_ingredient foreign key (idingredient) references ingredient(idingredient)
);