# Déclarer les clés étrangères pour la base facsys :
# exemple variant les RESTRICT, CASCADE et SET NULL

ALTER TABLE articles
ADD CONSTRAINT fk1 FOREIGN KEY fk_art_cat (idcategorie) REFERENCES categories (idcategorie)
ON DELETE SET NULL  ON UPDATE RESTRICT;

ALTER TABLE commandes
ADD CONSTRAINT fk2 FOREIGN KEY fk_com_cli (idclient) REFERENCES clients (idclient)
ON DELETE RESTRICT  ON UPDATE RESTRICT;

ALTER TABLE details
ADD CONSTRAINT fk4 FOREIGN KEY fk_det_com (numcommande) REFERENCES commandes (numcommande)
ON DELETE CASCADE  ON UPDATE RESTRICT;








# Démonstration des transactions 
# ...

start transaction ;

insert into categories values (null, 'Equitation', 'cheval, ane, poney...');

insert into articles set codearticle='SEL001', nom='Selle de cheval', prix=199.00, stock=2, idcategorie=LAST_INSERT_ID();

commit ;


start transaction ;

insert into categories values (null,'Athlétisme','course et lancers');

savepoint ouf ;

insert into categories values (null,'Sports collectifs','foot, rugby, hand, basket, volley');

insert into categories values (null,'toto', 'tata', 'titi') ;

rollback;
