Contraintes d'intégrité [Création et alimentation de bases de données SQL]

Contraintes d'intégrité

Objectifs

Connaître les différentes contraintes de cohérence sur les données ;
Connaître la syntaxe SQL pour ajouter des contraintes.

Mise en situation

Lors de la création d'une table, les attributs et leurs types ne suffisent pas à exprimer les contraintes qui existent sur les données.

Imaginez par exemple une table qui stocke des relevés de température pour différentes villes, chaque jour. On aura une table qui stocke les relevés, et une table qui stocke les villes.

Dans cette situation, il y a des contraintes fortes : il ne peut pas y avoir plus d'un relevé par jour pour une ville et il faut que la ville concernée existe dans la base.

Alors comment exprimer ces obligations que doivent respecter les données insérées, et qui assurent leur cohérence ? C'est ce que vous allez découvrir dans ce module.

Fondamental :

PRIMARY KEY (<liste d'attibuts>)
UNIQUE (<liste d'attibuts>)
FOREIGN KEY (<liste d'attibuts>) REFERENCES <nom table>(<nom colonnes>)
CHECK (<condition>)

Une contrainte d'intégrité est une règle qui définit la cohérence d'une donnée ou d'un ensemble de données de la BD^[1].

On définit généralement des contraintes sur les tables au moment de leur création.

Définition : Contraintes d'intégrité

Les contraintes d'intégrité sur une table sont :

PRIMARY KEY (<liste d'attibuts>) : définit les attributs de la liste comme la clé primaire.
UNIQUE (<liste d'attibuts>) : interdit que deux tuples de la relation aient les mêmes valeurs pour l'ensemble des attributs de la liste.
FOREIGN KEY (<liste d'attibuts>) REFERENCES <nom table>(<nom colonnes>) : contrôle l'intégrité référentielle entre les attributs de la liste et la table et ses colonnes spécifiées
CHECK (<condition>) : contrôle la validité de la valeur des attributs spécifiés dans la condition dans le cadre d'une restriction de domaine

Syntaxe :

CREATE TABLE nom_table (
nom_colonne1 domaine1,
nom_colonne2 domaine2,
...
nom_colonneN domaineN,
<contraintes de table>
);

CREATE TABLE nom_table (
nom_colonne1 domaine1,
nom_colonne2 domaine2,
...
nom_colonneN domaineN,
<contraintes de table>
);

Exemple :

CREATE TABLE personne (
n_ss CHAR(13) ,
nom VARCHAR(25) NOT NULL,
prenom VARCHAR(25) NOT NULL,
age INTEGER,
mariage CHAR(13),
codepostal INTEGER,
pays VARCHAR(50),
PRIMARY KEY (n_ss),
UNIQUE (nom, prenom),
CHECK (age BETWEEN 18 AND 65),
FOREIGN KEY (mariage) REFERENCES Personne(n_ss)
);

CREATE TABLE personne (
n_ss CHAR(13) ,
nom VARCHAR(25) NOT NULL,
prenom VARCHAR(25) NOT NULL,
age INTEGER,
mariage CHAR(13),
codepostal INTEGER,
pays VARCHAR(50),
PRIMARY KEY (n_ss),
UNIQUE (nom, prenom),
CHECK (age BETWEEN 18 AND 65),
FOREIGN KEY (mariage) REFERENCES Personne(n_ss)
);

Dans la définition de schéma précédente on a posé les contraintes suivantes :

La clé primaire de Personne est n_ss et la clé primaire de Adresse est (cp, pays).
nom, prenom ne peuvent pas être null et (nom, prenom) est une clé.
Age doit être compris entre 18 et 65 et Initiale doit être la première lettre de Pays (avec la fonction LEFT qui renvoie la sous chaîne à gauche de la chaîne passée en premier argument, sur le nombre de caractères passés en second argument)
mariage est clé étrangère vers Personne et (codepostal, pays) est une clé étrangère vers Adresse.

Méthode : Clé candidate

La clause UNIQUE associée à NOT NULL sur un attribut ou un groupe d'attributs définit une clé candidate non primaire.

À condition qu'aucun des attributs du groupe ne soit lui même UNIQUE (sinon la clause de minimalité d'une clé n'est pas respectée).

Complément : Contraintes d'intégrité sur une colonne

Lorsque la contrainte ne s'applique que sur une colonne, il existe une syntaxe dédiée

PRIMARY KEY : définit l'attribut comme la clé primaire.
UNIQUE : interdit que deux tuples de la relation aient la même valeur pour l'attribut..
REFERENCES <nom table> (<nom colonnes>) : contrôle l'intégrité référentielle entre l'attribut et la table et ses colonnes spécifiées.
CHECK (<condition>) : contrôle la validité de la valeur de l'attribut spécifié dans la condition dans le cadre d'une restriction de domaine.

Exemple : Réécriture avec uniquement des contraintes de colonnes

CREATE TABLE Personne (
  n_ss CHAR(13) PRIMARY KEY,
  nom VARCHAR(25) NOT NULL,
  prenom VARCHAR(25) NOT NULL,
  age INTEGER CHECK (age BETWEEN 18 AND 65),
  mariage CHAR(13) REFERENCES Personne(n_ss),
  codepostal INTEGER,
  pays VARCHAR(50),
  UNIQUE (nom, prenom)
);

CREATE TABLE Personne (
  n_ss CHAR(13) PRIMARY KEY,
  nom VARCHAR(25) NOT NULL,
  prenom VARCHAR(25) NOT NULL,
  age INTEGER CHECK (age BETWEEN 18 AND 65),
  mariage CHAR(13) REFERENCES Personne(n_ss),
  codepostal INTEGER,
  pays VARCHAR(50),
  UNIQUE (nom, prenom)
);

Ce schéma est strictement le même que le précédent, simplement les contraintes ont toutes été réécrites comme des contraintes de colonnes.