James Hoffman

Techniques de l'Informatique - Cégep de Shawinigan

Menu

Base de données 1

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

sql
copier
drop database if exists school;
create database school;

create 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 is_adult check(year(birthday) < (2022 - 18)), -- !!!, curdate() non-deterministe :(
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

sql
copier
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

sql
copier
drop database if exists school;
create database school;

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 d'encapsuler un SELECT dans un mécanisme réutilisable sous forme de table

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

sql
copier
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 sous-requêtes dans le FROM -> utiliser WITH
  • On ne peut pas associer de déclencheurs à une vue

sql
copier
drop database if exists school;
create database school;

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;