Contraintes d’intégrité

Introduction

L'objectif des contraintes d'intégrité est d'assurer la cohérence logique de la Base de Données. Une contrainte d'intégrité est une assertion vérifiée par les données de la base,
à tout moment. La conception de la base de données comprend non seulement un schéma relationnel mais aussi un ensemble de Contraintes d'Intégrité
(CIs).

Classification des CIs

CIs structurelles

 -> liées au modèle relationnel

-> unicité de valeur de clé, ...

CIs comportementales

-> liées aux applications

-> "La moyenne des salaires n'est pas inférieur à
10_000"

CIs intra-relation

Une CI intra-relation met en jeu une seule relation :

  • CI de domaine : "Le degré d'un vin varie entre 5 et 15 degrés"
  • CI de non nullité : "Toute valeur d'un numéro de vin est connue"
  • CI d'unicité de clé : "Tous les numéros de vin sont différents"
  • CI de dépendance donctionnelle : "Le numéro de vin détermine le cru"
  • CI temporelle : "Le degré d'un vin ne peut que décroître"
  • CI d'agrégat : "La moyenne des quantités de vin bu par tous les buveurs ne doit
    pas excéder 10 litres"

CIs inter-relation

Une CI inter-relation met en jeu plusieurs relations :

  • CI référentielle : notion de clé étrangère. Ce type de CI est développé ci-après.
  • CI d'inclusion : l'ensemble des valeurs d'une colonne d'une relation est inclus dans
    l'ensemble des valeurs d'une colonne d'une autre relation. "Toute ville de résidence d'un buveur est une ville de
    résidence d'un producteur" ;
  • CI générale : "La somme des quantités livrées à un buveur est
    inférieur ou égal à la somme des quantités
    commandées par ce buveur"

CI référentielle

Une CI référentielle est un attribut (ou groupe d'attrs) d'une relation apparaît comme
clé dans une autre relation. Par exemple : "Une récolte doit référencer un vin et un producteur
existants".

La définition d'un CI référentielle entraîne un certain nombre de vérifications :  

  1. Insertion d'une récolte -> le vin et le producteur doivent exister
  2. Suppression d'un vin -> ce vin ne doit pas être récolté

Comment sont prises en compte les CIs dans les SGBDs ?

Quand les déclarer ?

Les CIs peuvent être déclarées dès la création du schéma de la BD (lors des CREATE TABLE). Mais ce schéma peut évoluer et les contraintes sur les données aussi. Il est donc heureusement possible d'ajouter, de mofifier, de supprimer des CIs tout au long de la vie d'une BD.

Comment les exprimer ?

Le seul langage d'interaction avec un SGBD reste SQL. Les CIs sont donc elles aussi exprimées en SQL ou à l'aid ed'extension de SQL.
Certaines extensions de SQL permettent aussi d'exprimer

Qu'offrent les SGBD concrètement ?

Les SGBD prennent finalement en charge peu de CIs (unicité valeur, non nullité, ...).Toutes les autres CIs définies lors de la conception de la BD doivent alors être prises en charge dans les programmes développés au dessus de la BD.

les CIs dans les normes SQL
  • SQL 86 : unicité de valeur, non nullité, vue avec "check option"
  • SQL 89 : domaine, clé, intégrité
    référentielle avec "rejet"
  • SQL2 : intégrité référentielle avec "cascade delete et update"
 Ingres
  • extension de SQL
  • n'importe quelle CI mono-rel
  • n'importe quelle CI multi-relations avec les procédures BD et les règles

Exemples de CIs en SQL

SQL 86

CREATE TABLE employe (

nom CHAR(20) UNIQUE NOT NULL,

sal INTEGER,

age INTEGER,

dir CHAR(20) ) ;

SQL 89

CREATE TABLE employe (

nom CHAR(20) PRIMARY KEY,

sal INTEGER CHECK  (sal > 0),

dpt CHAR20) REFERENCES depart(nom) ) ;

 

CREATE TABLE depart (

nom CHAR(20) PRIMARY KEY,

nb_e INTEGER CHECK  (nb_e > 0) ) ;

SQL 92

CREATE TABLE employe (

nom CHAR(20) PRIMARY KEY,

sal INTEGER CHECK (sal > 0),

dpt CHAR(20) REFERENCES depart(nom) ON DELETE CASCADE ) ;

Vous pouez aussi regarder l'exemple de Exemples de CIs avec Ingres

create integrity on EMPLOYE is SAL > 10_000 and AGE >= 18 and AGE <= 60
create integrity on EMPLOYE is 
SAL > 10_000 
or SAL is null

range of E,M is EMPLOYE

create integrity on EMPLOYE is E.SAL <= M.SAL or E.DIR /= M.NOM

Problèmes avec les CIs

Cohérence

Les Cis ne doivent pas définir de règles contradictoires.

Redondance

Les CIs ne doivent pas être redondantes.

VIN.Degré < 15 et VIN.Degré < 14 <==> VIN.Degré < 14

Optimisation

La
vérification des CIs en permanence est extrêmement lourde. Des
politiques de sélection des CI à vérifier très subtils ont été définis
de façon à conserver des performances intéressantes. Il s'agit de
délimiter le nombre minimal de données mises en jeu pour la
vérification, effectuer la vérification sur certains types de mises à jour
uniquement.

Notion de transaction

Une base de données est dite cohérente si toutes les CIs sont vérifiées.

Une transaction est une unité d'exécution atomique pour le SGBD. Il s'agit d' un ensemble de requêtes SQL qui s'exécute en "tout ou
rien". Cet ensemble doit faire passer la BD d'un état cohérent à un
autre état cohérent.

Contexte de vérification des CIs

Les CIs doivent être vérifiées lorsqu'il s'git d'une transaction avec mise à jour.

Vérification immédiate

Dans le cas de la vérification immédiate les CIs sont évaluées avant chaque requête de
mise à jour.

si les CIs sont vérifiées

alors la mise à jour est faite

sinon la mise à jour est rejetée

Les CIs "simples (mono-relation : domaine, unicité, ...) répondent à ce mécanisme.

Vérification différée

Dans le cas de la vérification différée, les mises à jour sont exécutées. Les CIs sont évaluées à la fin de la
transaction : les mises à jour ne satisfaisant pas les CIs sont
défaites. Les CIs complexes (CIs référentielle, générale) répondent à ce mécanisme.

Notion de trigger

Définition

Un trigger est une règle spécifiant une action à exécuter sur la BD, quand une condition est vérifiée, suite à une mise à jour ou une interrogation. Ce mécanisme est aussi nommé déclencheur. Un trigger est de la forme :

sur <événement>

si <condition>

alors <action>

Exemple

sur MAJ de la relation PRODUIT

si PRODUIT.QTE < SEUIL

alors passer une commande du produit

Posté le 28 août 2009