Contraintes CHECK

Les CHECK permettent d'ajouter des validations effectuées avant une insertion ou une modification dans une table.

• Est une expression conditionnelle
  si elle retourne faux, l'opération en cours est annulée
• Applicable uniquement aux colonnes de la table où elle est définie
• Peuvent utiliser des fonctions déterministes
• Lorsque définie sur une colonne, ne devrait que manipuler celle-ci
  (pas en mariadb, mais généralement une bonne pratique)
• Lorsque définie sur une table, peut manipuler plusieurs colonnes

📚 CHECK constraints

CREATE OR REPLACE TABLE teachers (
  employee_number INT AUTO_INCREMENT KEY,

  name VARCHAR(200) NOT NULL CHECK(TRIM(name) != ''),

  email VARCHAR(200) UNIQUE NOT NULL CHECK( email REGEXP '^[^@]+@cshawi\\.ca$'),

  birthday DATE NOT NULL,
    CONSTRAINT is_adult CHECK(YEAR(birthday) < (2021 - 18)), -- CURDATE() non-deterministe :(
  death DATE,

  CONSTRAINT death_after_birth CHECK(death > birthday)
);

INSERT INTO teachers (name, email, birthday, death) 
VALUES  
--  (' ', 'jh@cshawi.ca', '2021-01-01', null); -- name
--  ('James', 'jh@hotmail.com', '2021-01-01', null); -- email
--  ('James', 'j@h@cshawi.ca', '2021-01-01', null); -- email
--  ('James', 'jh@cshawi.ca', '2021-01-01', null); -- birthday
--  ('James', 'jh@cshawi.ca', '2002-01-01', '2001-01-01'); -- death
    ('James', 'jh@cshawi.ca', '2002-01-01', null); -- OK

Déclencheurs

Un type d'objet créé dans la base de données permettant de réagir aux événements INSERT, UPDATE, DELETE d'une table

CREATE [OR REPLACE] TRIGGER [IF NOT EXISTS] trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW [ { FOLLOWS | PRECEDES } other_trigger_name ]
BEGIN
    sql_query;
    ...;
    other_sql_query;
END;

-- Liste
SHOW TRIGGERS;

-- Suppression
DROP TRIGGER trigger_name;

📚 CREATE TRIGGER

📚 Multiple statements triggers

• Les time permis sont BEFORE ou AFTER
• Les event permis sont INSERT, UPDATE, DELETE
• On peut générer une erreur dans un déclencheur pour interrompre l'événement AVANT qu'il ne se produise, ex: BEFORE INSERT
  

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Le message d\'erreur';

• Il est possible d'accéder aux valeurs des colonnes via les identifiants
  • NEW pour un INSERT ou UPDATE
  • OLD pour un UPDATE ou DELETE

OLD.email -- pour lire une colonne

• Les déclencheurs sont exécutés individuellement pour chaque ligne affectée
• Un déclencheur ne retourne pas de lignes, mais peut INSERT, UPDATE, DELETE
• Les actions ON DELETE/ON UPDATE des clés étrangères ne déclenchent PAS les triggers
• On peut identifier plusieurs déclencheur pour les mêmes time et event
• On peut utiliser des mécanismes standards de programmation
  (if, boucle, variables) dans les déclencheurs
  📚 Programmatic statements

CREATE OR REPLACE TABLE teachers (
  employee_number INT AUTO_INCREMENT KEY,

  name VARCHAR(200) NOT NULL CHECK(TRIM(name) != ''),

  email VARCHAR(200) UNIQUE NOT NULL CHECK( email REGEXP '^[^@]+@cshawi\.ca'),

  birthday DATE NOT NULL, -- Validation dans le TRIGGER
  death DATE,

  CONSTRAINT death_after_birth CHECK(death > birthday)
);

CREATE TRIGGER teachers_is_adult BEFORE INSERT ON teachers FOR EACH ROW 
BEGIN
    IF(TIMESTAMPDIFF(YEAR, NEW.birthday, CURDATE()) < 18) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Teacher is under 18 years old.';
    END IF;
END;

INSERT INTO teachers (name, email, birthday, death) 
VALUES  
    ('James', 'jh@cshawi.ca', '2021-01-01', null); -- birthday
--  ('James', 'jh@cshawi.ca', '2002-01-01', null); -- OK

Vues

Permet de créer une table virtuelle à partir d'un SELECT

• Réutiliser les requêtes SQL complexes
• Minimiser l'impact des modification à la structure de la base de données

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] view_name [(column_list)] AS 
select_statement;

-- Voir la définition

SHOW CREATE VIEW view_name;

-- Liste
SHOW TABLES;

-- Voir JUSTE les vues
SHOW FULL TABLES WHERE Table_type = 'VIEW'; 

-- Suppression
DROP VIEW [IF EXISTS] view_name [, view_name, ...]

📚 CREATE VIEW

• À la création de la vue, la structure des colonnes est figée dans le temps
  • Un SELECT * devient une énumération explicite, donc de nouvelles colonnes dans la BD n'impactent pas la vue
  • Le SELECT ne doit pas générer d'erreurs
• Pas de tables dérivées(sous-requête dans le FROM) -> utiliser WITH
• On ne peut pas associer de déclencheurs à une vue

CREATE OR REPLACE TABLE teachers (
  employee_number INT AUTO_INCREMENT KEY,
  name VARCHAR(200) NOT NULL CHECK(TRIM(name) != ''),
  email VARCHAR(200) UNIQUE NOT NULL CHECK( email REGEXP '^[^@]+@cshawi\.ca'),
  birthday DATE NOT NULL,
  death DATE,
  CONSTRAINT death_after_birth CHECK(death > birthday)
);

INSERT INTO teachers (name, email, birthday, death) 
VALUES ('James', 'jh@cshawi.ca', '2002-01-01', null);

-- Vue
CREATE OR REPLACE VIEW v_teachers AS 
SELECT *, (TIMESTAMPDIFF(YEAR, birthday, CURDATE())) AS `age` FROM teachers;

-- Utilisation, comme un table
SELECT * FROM v_teachers;