6 Gestion des contraintes dintgrit en SQL n

  • Slides: 48
Download presentation
6 Gestion des contraintes d’intégrité en SQL n Contrainte d'intégrité statique – respectée pour

6 Gestion des contraintes d’intégrité en SQL n Contrainte d'intégrité statique – respectée pour chacun des états de la BD – mécanismes déclaratifs n – procédural n n PRIMARY KEY, UNIQUE, NOT NULL, DOMAIN, FOREIGN KEY, CHECK, ASSERTION TRIGGER (SQL: 1999) Contrainte d'intégrité dynamique 26/11/2020 – contrainte sur changements d'états – référence aux états successifs – TRIGGER, REFERENCES ON DELETE…, ON UPDATE. . . © Robert Godin. Tous droits réservés. 1

6. 1 Contrainte de domaine n Types SQL – – – INTEGER CHAR. .

6. 1 Contrainte de domaine n Types SQL – – – INTEGER CHAR. . . NOT NULL n CHECK n CREATE DOMAIN n 26/11/2020 © Robert Godin. Tous droits réservés. 2

6. 1. 1 n Contrainte NOT NULL Par défaut : NULL 26/11/2020 © Robert

6. 1. 1 n Contrainte NOT NULL Par défaut : NULL 26/11/2020 © Robert Godin. Tous droits réservés. 3

6. 1. 2 Contrainte CHECK sur une colonne n Le no. Client est supérieur

6. 1. 2 Contrainte CHECK sur une colonne n Le no. Client est supérieur à 0 et inférieur à 100, 000 26/11/2020 © Robert Godin. Tous droits réservés. 4

6. 1. 3 Création d'un domaine (CREATE DOMAIN) Pas Oracle. . . 26/11/2020 ©

6. 1. 3 Création d'un domaine (CREATE DOMAIN) Pas Oracle. . . 26/11/2020 © Robert Godin. Tous droits réservés. 5

6. 1. 4 26/11/2020 Valeur de défaut (DEFAULT) © Robert Godin. Tous droits réservés.

6. 1. 4 26/11/2020 Valeur de défaut (DEFAULT) © Robert Godin. Tous droits réservés. 6

6. 2 Contrainte de clé primaire (PRIMARY KEY) n La clé primaire de la

6. 2 Contrainte de clé primaire (PRIMARY KEY) n La clé primaire de la table Client est le no. Client 26/11/2020 © Robert Godin. Tous droits réservés. 7

Clé primaire composée 26/11/2020 © Robert Godin. Tous droits réservés. 8

Clé primaire composée 26/11/2020 © Robert Godin. Tous droits réservés. 8

6. 3 Autres clés uniques (UNIQUE) 26/11/2020 © Robert Godin. Tous droits réservés. 9

6. 3 Autres clés uniques (UNIQUE) 26/11/2020 © Robert Godin. Tous droits réservés. 9

6. 4 Contrainte d'intégrité référentielle (FOREIGN KEY REFERENCES) n Le no. Client de la

6. 4 Contrainte d'intégrité référentielle (FOREIGN KEY REFERENCES) n Le no. Client de la table Commande fait référence à la clé primaire no. Client de la table Client n La table Client doit d ’abord être créée – n privilège REFERENCES sur Client PRIMARY KEY ou UNIQUE © Robert Godin. Tous droits réservés. 26/11/2020 10

6. 4. 1 Politique de gestion de la contrainte d'intégrité référentielle n Tentative de

6. 4. 1 Politique de gestion de la contrainte d'intégrité référentielle n Tentative de mise à jour de la clé primaire n Options – – 26/11/2020 NO ACTION CASCADE SET NULL SET DEFAULT © Robert Godin. Tous droits réservés. 11

6. 4. 1. 1 Politique NO ACTION Rejet d ’une violation de la contrainte

6. 4. 1. 1 Politique NO ACTION Rejet d ’une violation de la contrainte n Clause de défaut n 26/11/2020 © Robert Godin. Tous droits réservés. 12

Cas du UPDATE 26/11/2020 © Robert Godin. Tous droits réservés. 13

Cas du UPDATE 26/11/2020 © Robert Godin. Tous droits réservés. 13

6. 4. 1. 2 Politique CASCADE n Modification en cascade 26/11/2020 © Robert Godin.

6. 4. 1. 2 Politique CASCADE n Modification en cascade 26/11/2020 © Robert Godin. Tous droits réservés. 14

ON UPDATE CASCADE 26/11/2020 © Robert Godin. Tous droits réservés. 15

ON UPDATE CASCADE 26/11/2020 © Robert Godin. Tous droits réservés. 15

6. 4. 1. 3 Politiques SET NULL et SET DEFAULT 26/11/2020 © Robert Godin.

6. 4. 1. 3 Politiques SET NULL et SET DEFAULT 26/11/2020 © Robert Godin. Tous droits réservés. 16

SET DEFAULT 26/11/2020 © Robert Godin. Tous droits réservés. 17

SET DEFAULT 26/11/2020 © Robert Godin. Tous droits réservés. 17

6. 4. 1. 4 Clause MATCH PARTIAL/FULL 26/11/2020 © Robert Godin. Tous droits réservés.

6. 4. 1. 4 Clause MATCH PARTIAL/FULL 26/11/2020 © Robert Godin. Tous droits réservés. 18

Oracle n Défaut – – n ON DELETE NO ACTION ON UPDATE NO ACTION

Oracle n Défaut – – n ON DELETE NO ACTION ON UPDATE NO ACTION Supporte aussi – – 26/11/2020 ON DELETE CASCADE ON DELETE SET NULL (version 8 i) © Robert Godin. Tous droits réservés. 19

6. 5 Autres contraintes CHECK au delà d ’une colonne n ASSERTION générale n

6. 5 Autres contraintes CHECK au delà d ’une colonne n ASSERTION générale n 26/11/2020 © Robert Godin. Tous droits réservés. 20

6. 5. 1 CHECK intra-ligne Plusieurs colonnes de la même ligne n Les Articles

6. 5. 1 CHECK intra-ligne Plusieurs colonnes de la même ligne n Les Articles dont le no. Article est supérieur à 90 ont un prix supérieur à $15. 00 n 26/11/2020 © Robert Godin. Tous droits réservés. 21

6. 5. 2 Check inter-ligne d'une même table n Concerne plusieurs lignes Le prix.

6. 5. 2 Check inter-ligne d'une même table n Concerne plusieurs lignes Le prix. Unitaire d'un Article ne peut dépasser le prix moyen de plus de $40. 00 n Vérifié uniquement pour la ligne touchée n – n La contrainte peut être violée ! Pas supporté par Oracle 26/11/2020 © Robert Godin. Tous droits réservés. 22

6. 5. 3 CHECK inter-tables n Concerne plusieurs tables n Vérifié uniquement pour la

6. 5. 3 CHECK inter-tables n Concerne plusieurs tables n Vérifié uniquement pour la ligne touchée – La contrainte peut être violée ! n Pas supporté par Oracle 26/11/2020 © Robert Godin. Tous droits réservés. 23

6. 5. 4 n Assertions générales Le prix. Unitaire moyen d'un Article ne peut

6. 5. 4 n Assertions générales Le prix. Unitaire moyen d'un Article ne peut dépasser $25. 00 Toujours valide par opposition au CHECK n Non supporté par Oracle n 26/11/2020 © Robert Godin. Tous droits réservés. 24

Assertion inter-tables n La somme des quantitéLivrées pour une Ligne. Commande ne peut dépasser

Assertion inter-tables n La somme des quantitéLivrées pour une Ligne. Commande ne peut dépasser la quantité commandée 26/11/2020 © Robert Godin. Tous droits réservés. 25

6. 6 Implémentation de la vérification des contraintes d'intégrité Problème non trivial n Vérifier

6. 6 Implémentation de la vérification des contraintes d'intégrité Problème non trivial n Vérifier uniquement les lignes modifiées n Simplification différentielle n Simplification sémantique n – 26/11/2020 mécanismes d ’inférence © Robert Godin. Tous droits réservés. 26

6. 7 Cohérence des contraintes d'intégrité n Impossible de mettre à jour Article avec

6. 7 Cohérence des contraintes d'intégrité n Impossible de mettre à jour Article avec : Problème difficile en général n Aucune ou peu de vérification dans les SGBD actuels n 26/11/2020 © Robert Godin. Tous droits réservés. 27

6. 8 Nom de contrainte (clause CONSTRAINT) DROP CONSTRAINT cont. No. Client n SET

6. 8 Nom de contrainte (clause CONSTRAINT) DROP CONSTRAINT cont. No. Client n SET CONSTRAINT cont. No. Client … n Identification de la contrainte qui est violée à l ’exécution n 26/11/2020 © Robert Godin. Tous droits réservés. 28

6. 9 Contraintes déférées (SET CONSTRAINTS DEFERRED) n Quand vérifier ? n Une Commande

6. 9 Contraintes déférées (SET CONSTRAINTS DEFERRED) n Quand vérifier ? n Une Commande ne peut exister sans Ligne. Commande associée n Vérification immédiate par défaut – 26/11/2020 impossible d ’ajouter une Commande + ses Ligne. Commandes … © Robert Godin. Tous droits réservés. 29

Clause DEFERRABLE n Solution au problème : n Ou INITIALLY DEFERRED 26/11/2020 © Robert

Clause DEFERRABLE n Solution au problème : n Ou INITIALLY DEFERRED 26/11/2020 © Robert Godin. Tous droits réservés. 30

6. 10 Gâchettes (TRIGGER) n Procédure – – n n déclenchée par événement pré-déterminé

6. 10 Gâchettes (TRIGGER) n Procédure – – n n déclenchée par événement pré-déterminé (INSERT, DELETE, UPDATE) exécutée au niveau serveur de BD BD active Utilité – maintien de contraintes d ’intégrité n n n statique dynamique alternative aux mécanismes déclaratifs (CHECK, ASSERTION, . . . ) – – maintien d ’éléments dérivés n n n – 26/11/2020 colonnes dérivées copies dans BD répartie. . . historique des mises à jour n – préférer mécanisme déclaratif AUDIT sécurité © Robert Godin. Tous droits réservés. 31

Lorsqu'une augmentation du prix. Unitaire d'un Article est tentée, il faut limiter l'augmentation à

Lorsqu'une augmentation du prix. Unitaire d'un Article est tentée, il faut limiter l'augmentation à 10% du prix en cours 26/11/2020 © Robert Godin. Tous droits réservés. 32

6. 10. 1 Utilisation d'un TRIGGER pour le maintien d'une contrainte d'intégrité dynamique n

6. 10. 1 Utilisation d'un TRIGGER pour le maintien d'une contrainte d'intégrité dynamique n Empêcher une augmentation du prix. Unitaire d'un Article au delà de 10% du prix en cours n Oracle – 26/11/2020 RAISE_APPLICATION_ERROR © Robert Godin. Tous droits réservés. 33

6. 10. 2 Utilisation d'un TRIGGER pour le maintien d'une contrainte d'intégrité statique n

6. 10. 2 Utilisation d'un TRIGGER pour le maintien d'une contrainte d'intégrité statique n 0 < no. Client < 100000 n N. B. CHECK est préférable ! 26/11/2020 © Robert Godin. Tous droits réservés. 34

6. 10. 3 Étude de cas n Lors d'une nouvelle livraison, la quantité à

6. 10. 3 Étude de cas n Lors d'une nouvelle livraison, la quantité à livrer ne peut dépasser la quantité en stock disponible 26/11/2020 © Robert Godin. Tous droits réservés. 35

CHECK SQL 2 inadéquat 26/11/2020 © Robert Godin. Tous droits réservés. 36

CHECK SQL 2 inadéquat 26/11/2020 © Robert Godin. Tous droits réservés. 36

Lors d'une modification d'une quantitéLivrée, la différence entre la nouvelle quantitéLivrée et l'ancienne quantitée.

Lors d'une modification d'une quantitéLivrée, la différence entre la nouvelle quantitéLivrée et l'ancienne quantitée. Livrée doit être inférieure ou égale à la quantitéEn. Stock 26/11/2020 © Robert Godin. Tous droits réservés. 37

Ne permettre que la modification de la quantitéLivrée dans la table Détail. Liraison 26/11/2020

Ne permettre que la modification de la quantitéLivrée dans la table Détail. Liraison 26/11/2020 © Robert Godin. Tous droits réservés. 38

Ajuster la quantitéEn. Stock 26/11/2020 © Robert Godin. Tous droits réservés. 39

Ajuster la quantitéEn. Stock 26/11/2020 © Robert Godin. Tous droits réservés. 39

Extension procédurale pour corps. Trigger n n Traitements complexes Combiner plusieurs TRIGGER – n

Extension procédurale pour corps. Trigger n n Traitements complexes Combiner plusieurs TRIGGER – n vérifier quel est l ’événement déclencheur Ordre d ’exécution des TRIGGER – – BEFORE avant AFTER, . . . entre TRIGGER de même type ? n n forcer un ordre en combinant Oracle – 26/11/2020 PL/SQL © Robert Godin. Tous droits réservés. 40

6. 10. 4 TRIGGER de niveau STATEMENT n Exécution du corps une seule fois

6. 10. 4 TRIGGER de niveau STATEMENT n Exécution du corps une seule fois pour plusieurs lignes mises à jours dans le même énoncé 26/11/2020 © Robert Godin. Tous droits réservés. 41

6. 10. 5 Ordre d'exécution des TRIGGER n Attention aux circularités ! 26/11/2020 ©

6. 10. 5 Ordre d'exécution des TRIGGER n Attention aux circularités ! 26/11/2020 © Robert Godin. Tous droits réservés. 42

6. 10. 6 Limites des TRIGGER Ne peuvent être DEFERRED n Complexes à coder

6. 10. 6 Limites des TRIGGER Ne peuvent être DEFERRED n Complexes à coder n Contraintes particulières aux dialectes n 26/11/2020 © Robert Godin. Tous droits réservés. 43

6. 10. 7 Particularités des TRIGGER Oracle n n n Pas de SELECT dans

6. 10. 7 Particularités des TRIGGER Oracle n n n Pas de SELECT dans le WHEN : NEW, : OLD Omettre le mot-clé ROW dans REFERENCING Corps en PL/SQL (voir chapitre 4). Syntaxe : nom. Colonne Pas de COMMIT/ROLLBACK dans un TRIGGER – procédure PL/SQL RAISE_APPLICATION_ERROR n n n Intervalle [-20000, -20999] pour code d’erreur IF INSERTING, DELETING, UPDATING. Événements non standards – INSTEAD OF, STARTUP, LOGON, . . . Problème avec table en mutation (modifiée par l'événement déclencheur) n etc. 26/11/2020 © Robert Godin. Tous droits réservés. n 44

26/11/2020 © Robert Godin. Tous droits réservés. 45

26/11/2020 © Robert Godin. Tous droits réservés. 45

26/11/2020 © Robert Godin. Tous droits réservés. 46

26/11/2020 © Robert Godin. Tous droits réservés. 46

Trigger INSTEAD OF pour VIEW non modifiable (non standard SQL: 1999) 26/11/2020 © Robert

Trigger INSTEAD OF pour VIEW non modifiable (non standard SQL: 1999) 26/11/2020 © Robert Godin. Tous droits réservés. 47

suite 26/11/2020 © Robert Godin. Tous droits réservés. 48

suite 26/11/2020 © Robert Godin. Tous droits réservés. 48