DROP SCHEMA IF EXISTS CONFERENCE;
CREATE SCHEMA CONFERENCE;
USE CONFERENCE;

CREATE TABLE ARTICLE (
    idArticle INT PRIMARY KEY,
    titre TEXT,
    annee INT,
    auteur TEXT
);

CREATE TABLE EXAMINATEUR (
    idExaminateur INT PRIMARY KEY,
    nom TEXT
);

CREATE TABLE DECISION (
    idDecision INT PRIMARY KEY,
    idExaminateur INT,
    idArticle INT,
    vote INT,
    dateV date,
    CONSTRAINT cle1 FOREIGN KEY (idExaminateur)
        REFERENCES EXAMINATEUR (idExaminateur),
    CONSTRAINT cle2 FOREIGN KEY (idArticle)
        REFERENCES ARTICLE (idArticle)
);

INSERT INTO ARTICLE VALUES (1, 'Bees', 1939, 'Sam');
INSERT INTO ARTICLE VALUES (2, 'Plants', 1977, 'Elvia');
INSERT INTO ARTICLE VALUES (3, 'Yoga', 1965, 'Patrice');

INSERT INTO EXAMINATEUR VALUES (1, 'Barry');
INSERT INTO EXAMINATEUR VALUES (2, 'Ali');
INSERT INTO EXAMINATEUR VALUES (3, 'Samy');

INSERT INTO DECISION VALUES (1,1,1,2,'2013-02-22');
INSERT INTO DECISION VALUES (2,1,1,4,'2013-02-27');
INSERT INTO DECISION VALUES (3,2,3,5,'2013-02-10');
INSERT INTO DECISION VALUES (4,3,2,1,'2013-02-04');

DELETE FROM ARTICLE 
WHERE
    idArticle = 1;

SELECT * FROM DECISION;

-- La premiere ligne ne peut être supprimée 
-- car elle est référencée à partir de la table DECISION
-- et que le comportement de la clé est à RESTRICT

DELETE FROM DECISION 
WHERE
    idArticle = 1;
DELETE FROM ARTICLE 
WHERE
    idArticle = 1;

UPDATE ARTICLE 
SET 
    titre = 'Tropical Plants'
WHERE
    titre = 'Plants';

UPDATE ARTICLE 
SET 
    idArticle = 4
WHERE
    idArticle = 2;

-- La deuxième ligne ne peut être mise à jour 
-- car elle est référencée à partir de la table DECISION
-- et que le comportement est à RESTRICT

DELETE FROM DECISION 
WHERE
    idArticle = 2;
UPDATE ARTICLE 
SET 
    idArticle = 4
WHERE
    idArticle = 2;
INSERT INTO DECISION VALUES (4,3,4,1,'2013-02-04');

-- Remise à l'état initial

DELETE FROM DECISION;
DELETE FROM ARTICLE;

INSERT INTO ARTICLE VALUES (1, 'Bees', 1939, 'Sam');
INSERT INTO ARTICLE VALUES (2, 'Plants', 1977, 'Elvia');
INSERT INTO ARTICLE VALUES (3, 'Yoga', 1965, 'Patrice');
INSERT INTO DECISION VALUES (1,1,1,2,'2013-02-22');
INSERT INTO DECISION VALUES (2,1,1,4,'2013-02-27');
INSERT INTO DECISION VALUES (3,2,3,5,'2013-02-10');
INSERT INTO DECISION VALUES (4,3,2,1,'2013-02-04');

-- Changement des clés en SET NULL

ALTER TABLE DECISION DROP FOREIGN KEY cle1;
ALTER TABLE DECISION DROP FOREIGN KEY cle2;


ALTER TABLE DECISION 
	ADD CONSTRAINT cle3 FOREIGN KEY (idArticle) 
		REFERENCES ARTICLE(idArticle) 
			ON DELETE SET NULL ON UPDATE SET NULL;


ALTER TABLE DECISION 
	ADD CONSTRAINT cle4 FOREIGN KEY (idExaminateur) 
		REFERENCES EXAMINATEUR(idExaminateur) 
			ON DELETE SET NULL ON UPDATE SET NULL;


ALTER TABLE DECISION DROP FOREIGN KEY cle3;
ALTER TABLE DECISION DROP FOREIGN KEY cle4;

ALTER TABLE DECISION 
	ADD CONSTRAINT cle5 FOREIGN KEY (idArticle) 
		REFERENCES ARTICLE(idArticle) 
			ON DELETE CASCADE ON UPDATE CASCADE;


ALTER TABLE DECISION 
	ADD CONSTRAINT cle6 FOREIGN KEY (idExaminateur) 
		REFERENCES EXAMINATEUR(idExaminateur) 
			ON DELETE CASCADE ON UPDATE CASCADE;



