USE temp ;
DROP TABLE IF EXISTS events ;
DROP TABLE IF EXISTS customers ;
DROP TABLE IF EXISTS stores ;
DROP TABLE IF EXISTS vendors ;
DROP TABLE IF EXISTS users ;
---------------------------------------------------------------------------------
CREATE TABLE users
(
user_id INT NOT NULL,
username VARCHAR(64) NOT NULL,
mail VARCHAR(64) NOT NULL,
password VARCHAR(64) NOT NULL,
CONSTRAINT USERS_PK PRIMARY KEY (user_id)
) ;
CREATE TABLE vendors
(
user_id INT NOT NULL,
CONSTRAINT VENDORS_PK PRIMARY KEY (user_id),
CONSTRAINT VENDORS_USERS_FK FOREIGN KEY (user_id)
REFERENCES users (user_id)
) ;
CREATE TABLE stores
(
user_id INT NOT NULL,
store_id INT NOT NULL,
storename VARCHAR(64) NOT NULL,
CONSTRAINT STORES_PK PRIMARY KEY (user_id, store_id),
CONSTRAINT STORES_VENDORS_FK FOREIGN KEY (user_id)
REFERENCES vendors (user_id) ON DELETE CASCADE
) ;
CREATE TABLE customers
(
user_id INT NOT NULL,
CONSTRAINT CUSTOMERS_PK PRIMARY KEY (user_id),
CONSTRAINT CUSTOMERS_USERS_FK FOREIGN KEY (user_id)
REFERENCES users (user_id) ON DELETE CASCADE
) ;
CREATE TABLE events
(
user_id INT NOT NULL,
datum DATETIME NOT NULL,
CONSTRAINT EVENTS_PK PRIMARY KEY (user_id),
CONSTRAINT EVENTS_CUSTOMERS_FK FOREIGN KEY (user_id)
REFERENCES customers (user_id) ON DELETE CASCADE
) ;
DELIMITER GO
CREATE TRIGGER VENDORS_EXCLUSION_INSERT BEFORE INSERT ON vendors
FOR EACH ROW
BEGIN
SET @Kount = (SELECT COUNT(*) FROM customers WHERE user_id = NEW.user_id) ;
IF @Kount > 0 THEN
SET @Erreur = CONCAT('Le marchand d''identifiant "', NEW.user_id, '" existe déjà en tant que client. Rejet.') ;
SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @Erreur ;
END IF ;
END
GO
CREATE TRIGGER CUSTOMERS_EXCLUSION_INSERT BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
SET @Kount = (SELECT COUNT(*) FROM vendors WHERE user_id = NEW.user_id) ;
IF @Kount > 0 THEN
SET @Erreur = CONCAT('Le client d''identifiant "', NEW.user_id, '" existe déjà en tant que marchand. Rejet.') ;
SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = @Erreur ;
END IF ;
END
GO
DELIMITER ;
INSERT INTO users (user_id, username, mail, password) VALUES
(1, 'Tryphon Tournesol', 'tryphon@moulinsart.be', '********')
, (2, 'Fernand Naudin', 'fernand@citron.fr', '********')
, (3, 'Archibald Haddock', 'haddock@moulinsart.be', '********')
, (4, 'Raoul Volfoni', 'raoul@pamplemousse.fr', '********')
, (5, 'Paul Volfoni', 'paulo@pamplemousse.fr', '********')
, (6, 'Antoine Delafoy', 'antoine@mandarine.fr', '********')
, (7, 'Séraphin Lampion', 'seraphin@dvp.be', '********')
;
INSERT INTO vendors (user_id) VALUES
(2), (4), (5), (6)
;
INSERT INTO stores (user_id, store_id, storename) VALUES
(2, 1, 'Chez Fernand')
, (2, 2, 'Le clapier')
, (2, 3, 'Au bon coin')
, (2, 4, 'Chez Mimile')
, (4, 1, 'La Closerie')
, (4, 2, 'Chez René')
, (4, 3, 'Vive la vie')
, (5, 1, 'La cave à Irène')
, (5, 2, 'Le Villon')
, (6, 1, 'Pique à seaux')
, (6, 2, 'Atout pique')
;
INSERT INTO customers (user_id) VALUES
(1), (3), (7)
;
INSERT INTO events (user_id, datum) VALUES
(1, '2016-01-05')
, (3, '2016-02-29')
, (7, '2016-01-05')
;
-- Tentatives d’infraction :
INSERT INTO customers (user_id) VALUES (2), (6) ;
INSERT INTO vendors (user_id) VALUES (3), (7) ;