| 
La dernière normalisation de SQL (on parle de SQL-2), définit entre autre les contraintes d'intégrité de la base de données. Une containte doit être nommée afin que le gestionnaire renvoi un nom de contrainte significatif dans le message lors d'une violation. Cette nomination se fait par: <contrainte> CONSTRAINT nom. Contraintes de domaine: NOT NULL (variable obligatoire) create table article (CODART CHAR(6) NOT NULL , ...) DEFAUT valeur ou mot-réservé create table article (CODART CHAR(6) NOT NULL, DATCRT DATE DEFAUT CURRENT-DATE ) |
|
UNIQUE toute valeur doit être unique. Pour les clés candidates, mais il semble qu'un index fasse aussi bien l'affaire. CHECK(expression) expression devant être vraie CHECK(nom de zone op valeur) ! !-> opérateur logique (<,>,=,...) create table article (CODART CHAR(6) NOT NULL, QTESTOCK DEC(11, 2), CHECK(qtestock >= 0) CONSTRAINT qtestock_negative) CHECK(nom-de-zone op nom-de-zone) create table command (NUMCDE DEC(6, 0) NOT NULL, QTECDE DEC(11, 2), QTELIV DEC(11, 2), CHECK(qteliv <= qtecde) CONSTRAINT qteliv_invalide) |
|
CHECK(nom-de-zone IN (liste de valeurs))
create table personnel (MATRICUL DEC(6, 0),
SITUATION CHAR(1) /* marié,veuf,...*/,
CHECK(situation in ('M' 'V' 'C' 'D'))
CONSTRAINT situation_invalide)
CHECK(nom-de-zone BETWEEN(val1 AND val2))
la clause CHECK admet des sous-sélections
(approche référentielle)
Soit la table commande contenant un prix à la commande qui
ne peut pas être remisé de plus de 20% par rapport au tarif
create table command (NUMCDE DEC(6, 0) NOT NULL,
ARTCDE CHAR(6) , QTECDE DEC(11, 2),
PRICDE DEC(9, 2) NOT NULL,
CHECK(pricde >= 0,8 * (SELECT pritarif
FROM article
WHERE codart = artcde))
CONSTRAINT remise_invalide)
|
|
la clause CHECK admet des conditions complexes (reliées par OR ou bien AND) Soit la table commande contenant un prix à la commande qui DOIT être remisé d'au moins 10% si la quantité est > à 100 create table command ..... CHECK(qteliv < 100 OR pricde <= 0,9 * (SELECT pritarif FROM article WHERE codart = artcde)) CONSTRAINT remise_mini Contraintes d'entité: désignation d'une clé primaire (identifiant univoque). Create table (zone1 PRIMARY KEY , ...) ou create table (zone1, zone2, zone3, ... PRIMARY KEY(zone1, zone3) ) |
|
les clés primaires doivent être définies NOT NULL il était possible d'arriver au même résultat en SQL-89 (c'est toujours possible) en créant un index UNIQUE avec cette clé. cela offre probablement plus de souplesse. Les puriste considérent que cette contrainte étant sortie de la table, l'intégrité est moins forte. (suppression de l'index par exemple) Contraintes d'intégrité référentielle: Il s'agit de concrétiser le lien exitant entre deux tables. (ce qui était réalisé jusqu'ici par programme) soit par des déclarations de contraintes (plus performant) soit par des procédures associées à une action = trigger (plus souple, plus "gourmand"). |
|
Définitions clé candidate: attribut ou groupe d'attributs dans une table T1 susceptible d'identifier un enregistrement.Une clé primaire est toujours une clé candidate. clé étrangère: attribut ou groupe d'attributs dans une table T1 dont les valeurs doivent exister comme valeurs de la clé candidate dans T2. (T1 et T2 peuvent être la même table) table référencée: table T2 qui contient la clé candidate. table qui référence: table T1 qui contient la clé étrangère. Déclaration d'une contrainte d'intégrité référentielle: (dans la table qui référence) champs REFERENCES table-référencée(clé candidate) (clé candidate) est facultatif et inutile lors qu'il s'agit de la clé primaire de la table référencée. |
|
DU point de vue de la table qui référence: create table command (NUMCDE DEC(6, 0) NOT NULL, ARTCDE CHAR(6) REFERENCES article, NUMCLI DEC(6, 0) REFERENCES clients) ici le code article doit exister dans la table article le n° de client doit exister dans la table clients. ou bien (si la référence porte sur plusieurs colonnes) FOREIGN KEY(zone1, zone2, ..) REFERENCES table create table livraison (NUMLIV DEC(6, 0) NOT NULL, NUMCDE DEC(6, 0) NOT NULL, NOLIGN DEC(3, 0) NOT NULL, FOREIGN KEY(numcde, nolign) REFERENCES lcommand) ici une livraison doit faire référence à une ligne de commande existante. |
|
DU point de vue de la table référencée: il s'agit maintenant de définir ce que doit faire le gestionnaire en cas de manipulation de la table référencée. Pour le lien commande<-->client. que faire si l'on supprime un client si l'on change le code d'un client ... etc ... tout cela ce déclare sur la table qui référence: (ici command) NUMCLI DEC(6, 0) REFERENCES clients ON évènement action évènement : une action réalisée sur la table référencée (ici clients) ON UPDATE = clé modifiée ON DELETE = ligne supprimée |
|
action: que doit faire le SGBD sur la table qui référence (ici command) NO ACTION : ne rien faire, l'évènement est interdit. CASCADE : en cas de mise à jour,répercute la mise à jour sur la table qui référence. (en cas de modification d'une code client toutes ses commandes possèdent son nouveau code) en cas de suppression, suppression des lignes associées dans la table qui référence. (en cas de suppression d'un client, suppression de toutes ses commandes) SET NULL : l'évènement est autorisé et la clé étrangère de la table qui référence est mise à NULL (NULL doit être autorisé) SET DEFAULT : idem SET NULL avec la valeur/dft |
|
Exemple avec notre table command: NUMCLI DEC(6, 0) REFERENCES clients ON UPDATE CASCADE ON DELETE NO ACTION répercussion des modifs,suppression non autorisée. remarque NO ACTION équivaut à ne rien préciser (c'est le défaut) certains SGBD utilisent la clause RESTRICT à la place. Une contrainte d'intégrité peut être sortie de la notion de table (non rattachée à une table) avec la notion d'ASSERTION. CREATE ASSERTION nom-assertion CHECK(NOT EXIST (SELECT * from command WHERE pricde >= 0,8 * (SELECT pritarif FROM article WHERE codart = artcde)) Ce que nous avions déja définit, à la création de la table. |
|
Et enfin toute contrainte d'intégrité est par défaut immédiate (c'est à dire vérifiée à chaque instruction SQL) Elle peut être différée c'est à dire reportée à la fin de la transaction (ordre COMMIT) Prenons l'exmple suivant: 1/ Un client ne peut exister que s'il possède au moins une commande. 2/ Une commande ne peut être enregistrée que pour un client connu. Ces deux règles peuvent être dans certains cas (nouveau client) contradictoires et peuvent empecher la création d'une commande pour un nouveau client. En déclarant ces deux contraintes différées, il suffit de demander l'ajout du client et de sa première commande dans la même transaction. En fin de transaction (COMMIT), les deux règles s'avéreront vraies. |
|
Syntaxe: chaque contrainte peut être complétée par (NOT) DEFERRABLE -- INITIALLY DEFERRED / IMMEDIATE ! ! une contrainte pouvant être déclarée différable ! lors de sa création (non différable par défaut). ! -> il s'agit de la valeur initiale qui peut être modifiée en début de transaction pendant l'application par: SET CONSTRAINTS (<-liste de contraintes-> / ALL) DEFERRED / IMMEDIATE Mise en oeuvre de tout cela sur AS/400 via DB2/400 : |
|
Définition d'une clé primaire ADDPFCST FILE(client) TYPE(*PRIKEY) CST(num_client) <- nom de la contrainte KEY(nocli) Intégrité référentielle ADDPFCST FILE(command) TYPE(*REFCST) CST(clicommand) KEY(numcli) <- zone du fichier commande PRNFILE(client) <- fichier parent PRNKEY(nocli) <- clé candidate/fichier client UPDRULE(*NOACTION) DLTRULE(*CASCADE) UPDRULE : *NOACTION , *RESTRICT (= *NOACTION mais avant l'appel du trigger) DLTRULE : les mêmes + *CASCADE , *SETNULL , *SETDFT |
|
Ces contraintes peuvents être définies via SQL : CREATE TABLE modifié (conforme à la norme, sur ce point) ou par ALTER TABLE (uniquement pour ajouter une contrainte) Une contrainte peut être momentanement "suspendue" ou peut s'avérer fausse (restauration par exemple) Il y a 4 états possibles: - Etablie/active <-- OK - Etablie/desactivée <-- suspendue - Définie/activée <-- pas de membre - Définie/désactivée <-- en erreur Deux commandes permettent de gérer l'état : WRKPFCST et EDTPFCST appellé directement à l'IPL en cas de problème. |
|
Si une contrainte est fausse Pour une contrainte d'unicité : elle est réalisable immédiatement ou il y a echec. Pour une contrainte d'intégrité : - soit suite à une restauration - soit suite à une désactivation , puis modifs dans le fichier Elle est notées "Check Pending" Il faut utiliser la commande WRKPFCTS |
|
WRKPFCST ########################################################################## # Gestion des contraintes de fichier physique # # # # 2=Modifier 4=Enlever 6=Afficher enreg en instance de vérif. # # # # Opt Contrainte Fichier Biblio Type Etat verif/instance # # ARTICK1 ARTICP1 MABIB *PRIKEY # # ARTICC1 ARTICP1 MABIB *REFCST ETA/DES Oui # # ARTICC2 ARTICP1 MABIB *REFCST ETA/ACT Non # # .... # # ==> # # # ########################################################################## 2=CHGPFCST, 4=RMVPFCST "Verif : Oui" signifie que le système à détecté au moins un incohérence (visualisable par l'option 6) |
