Dans le monde de l'entreprise, nous devons nous assurer que certaines règles sont toujours respectées. Par exemple, un employé travaillant sur un projet doit être inscrit au registre du personnel. Ou, peut-être, certains événements doivent se produire de manière systématique. Par exemple, si un vendeur réalise une vente, sa commission doit être augmentée.
DB2 Universal Database propose un ensemble de méthodes très utiles pour garantir le respect de ces règles :
Cette première section présente une vue d'ensemble des clés. Dans les sections qui suivent, plusieurs exemples et diagrammes permettent d'étudier les possibilités offertes par l'intégrité référentielle, les contraintes et les déclencheurs.
Une clé est un ensemble de colonnes à utiliser pour identifier une ou plusieurs lignes ou pour y accéder.
Une clé composée de plusieurs colonnes est appelée clé composée. Dans une table à clé composée, l'ordre des colonnes dans la clé ne correspond pas nécessairement à leur ordre dans la table.
Une clé unique peut être définie comme une colonne (ou un ensemble de colonnes) qui ne peut pas contenir de valeurs en double. Les colonnes auxquelles s'applique une clé unique ne peuvent pas contenir de valeur NULL. Le respect de la contrainte est assuré par le gestionnaire de bases de données, pendant l'exécution des instructions INSERT et UPDATE. Plusieurs clés uniques peuvent être appliquées à une même table. Facultatives, les clés uniques peuvent être définies dans les instructions CREATE TABLE et ALTER TABLE.
Une clé primaire est une clé unique faisant partie de la définition de la table. Une table ne peut pas avoir plusieurs clés primaires, et les colonnes concernées par une clé primaire ne peuvent pas contenir de valeur NULL. Facultatives, les clés primaires peuvent être définies dans les instructions CREATE TABLE et ALTER TABLE.
Une clé associée fait partie de la définition d'une contrainte référentielle. Aucune, une ou plusieurs clés associées peuvent être appliquées à une table. La valeur de la clé associée composée est NULL si un des composants de la valeur est NULL. Facultatives, les clés associées peuvent être définies dans les instructions CREATE TABLE et ALTER TABLE.
Une contrainte d'unicité garantit que les valeurs d'une clé sont uniques dans une même table. Facultatives, les contraintes d'unicité peuvent être définies avec les instructions CREATE TABLE ou ALTER TABLE, en précisant une clause PRIMARY KEY ou UNIQUE. Par exemple, il est possible de définir une contrainte d'unicité sur la colonne EMPNO (matricules) d'une table, pour garantir que chaque numéro d'employé sera unique.
Les contraintes d'unicité et les clés associées permettent de définir les relations entre les tables et, par conséquent, de garantir le respect de certaines règles de gestion. Les contraintes utilisant des clés uniques et des clés associées sont couramment appelées contraintes d'intégrité référentielle. Une contrainte d'unicité référencée par une clé associée est appelée clé parente. Une clé associée se réfère à une clé parente spécifique, ou elle lui est reliée. Par exemple, une règle peut fixer que tous les employés (table EMPLOYEE) doivent appartenir à un service existant (table DEPARTMENT). Ainsi, le numéro de service défini dans la table EMPLOYEE sera utilisé comme clé associée et le numéro de service défini dans la table DEPARTMENT sera utilisé comme clé primaire. Le diagramme suivant illustre les contraintes d'intégrité référentielle :
![]() |
Les contraintes de vérification de table indiquent les conditions qui sont vérifiées pour chaque ligne d'une table. Il est possible d'indiquer des contraintes de vérification de table individuelles. Elles peuvent être ajoutées au moyen des instructions CREATE ou ALTER TABLE.
L'instruction ci-dessous crée une table devant respecter les contraintes suivantes :
CREATE TABLE EMP (ID SMALLINT NOT NULL, NAME VARCHAR(9), DEPT SMALLINT CHECK (DEPT BETWEEN 10 AND 100), JOB CHAR(5) CHECK (JOB IN ('Sales', 'Mgr', 'Clerk')), HIREDATE DATE, SALARY DECIMAL(7,2), COMM DECIMAL(7,2), CLE PRIMAIRE (ID), CONSTRAINT YEARSAL CHECK (YEAR(HIREDATE) >= 1986 OR SALARY > 40500) )
Il n'y a violation de contrainte que si l'évaluation donne un résultat faux. Par exemple, si DEPT a une valeur NULL sur une ligne insérée, l'insertion se fait sans erreur, bien que la contrainte demande que les numéros de service soient compris entre 10 et 100.
L'instruction suivante indique pour la table EMPLOYEE une contrainte COMP selon laquelle le salaire compensé total de l'employé doit dépasser 15000 F :
ALTER TABLE EMP ADD CONSTRAINT COMP CHECK (SALARY + COMM > 15000)
Le respect de la nouvelle contrainte doit également être vérifié dans les lignes existantes de la table. Il est possible de différer cette vérification, en utilisant l'instruction SET CONSTRAINTS de la manière suivante :
SET CONSTRAINTS FOR EMP OFF ALTER TABLE EMP ADD CONSTRAINT COMP CHECK (SALARY + COMM > 15000) SET CONSTRAINTS FOR EMP IMMEDIATE CHECKED
Tout d'abord, l'instruction SET CONSTRAINTS permet de différer la vérification des contraintes de la la table. Ensuite, une ou plusieurs contraintes peuvent être ajoutées à la table sans être vérifiées. Enfin, l'instruction SET CONSTRAINTS, émise une nouvelle fois, réactive la vérification de la table, pour que les vérifications différées, s'il y en a, aient lieu.
Un déclencheur définit un ensemble d'actions activé par une opération qui modifie les données dans la table de base précisée.
Les déclencheurs peuvent être utilisés pour :
Les déclencheurs permettent de développer plus rapidement des applications, d'imposer des règles de gestion globales et de faciliter la maintenance des applications et des données.
DB2 Universal Database accepte plusieurs types de déclencheurs. La définition des déclencheurs peut prévoir qu'ils soient activés avant ou après une opération DELETE, INSERT ou UPDATE. Chaque déclencheur comporte un ensemble d'instructions SQL, appelé action déclenchée, qui peut inclure une condition de recherche facultative.
La définition des déclencheurs AFTER peut également préciser que l'action doit être déclenchée à chaque ligne ou une seule fois pour l'instruction entière, tandis que les déclencheurs BEFORE appliquent toujours l'action déclenchée à chacune des lignes.
Un déclencheur placé avant une instruction INSERT, UPDATE ou DELETE permet de vérifier que certaines conditions sont remplies avant d'exécuter une opération de déclenchement ou de modifier des valeurs avant de les stocker dans la table.
Un déclencheur AFTER permet de transmettre des valeurs en fonction des besoins ou d'exécuter d'autres tâches nécessaires au cours de l'opération de déclenchement, comme l'envoi d'un message.
L'exemple suivant décrit l'utilisation de déclencheurs BEFORE et AFTER. Prenons le cas d'une application qui enregistre et suit les modifications des cours de la Bourse. La base de données contient deux tables, CURRENTQUOTE et QUOTEHISTORY :
CREATE TABLE CURRENTQUOTE (SYMBOL VARCHAR(10), QUOTE DECIMAL(5,2), STATUS VARCHAR(9)) CREATE TABLE QUOTEHISTORY (SYMBOL VARCHAR(10), QUOTE DECIMAL(5,2), TIMESTAMP TIMESTAMP)
Lorsque la colonne QUOTE de CURRENTQUOTE est mise à jour à l'aide d'une instruction telle que :
UPDATE CURRENTQUOTE SET QUOTE = 68.5 WHERE SYMBOLE = 'IBM'
La colonne STATUS de CURRENTQUOTE doit être mise à jour pour indiquer si la valeur de l'action :
Cette mise à jour s'effectue à l'aide du déclencheur BEFORE ci-dessous :
(1)
CREATE TRIGGER STOCK_STATUS NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE FOR EACH ROW MODE DB2SQL
(2)
SET NEWQUOTE.STATUS =
(3)
CASE
(4)
WHEN NEWQUOTE.QUOTE >= (SELECT MAX(QUOTE) FROM QUOTEHISTORY WHERE SYMBOL = NEWQUOTE.SYMBOL AND YEAR(TIMESTAMP) = YEAR(CURRENT DATE) ) THEN 'En hausse'
(5)
WHEN NEWQUOTE.QUOTE <= (SELECT MIN(QUOTE) FROM QUOTEHISTORY WHERE SYMBOL = NEWQUOTE.SYMBOL AND YEAR(TIMESTAMP) = YEAR(CURRENT DATE) ) THEN 'En baisse'
(6)
WHEN NEWQUOTE.QUOTE > OLDQUOTE.QUOTE THEN 'En hausse' WHEN NEWQUOTE.QUOTE < OLDQUOTE.QUOTE THEN 'En baisse' WHEN NEWQUOTE.QUOTE = OLDQUOTE.QUOTE THEN 'Stable' END
Outre la mise à jour de l'entrée dans la table CURRENTQUOTE, vous devez créer un enregistrement d'audit en copiant la nouvelle cotation ainsi que l'horodatage, dans la table QUOTEHISTORY. Cette mise à jour s'effectue à l'aide du déclencheur AFTER ci-dessous :
(1)
CREATE TRIGGER RECORD_HISTORY AFTER UPDATE OF QUOTE ON CURRENTQUOTE REFERENCING NEW AS NEWQUOTE FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
(2)
INSERT INTO QUOTEHISTORY VALUES (NEWQUOTE.SYMBOL, NEWQUOTE.QUOTE, CURRENT TIMESTAMP); END
CURRENT DATESTAMP est un registre spécial contenant l'horodatage. Vous trouverez une liste et une explication des registres à la section Registres spéciaux.