Gestion des contraintes

---

Dans ce chapitre, vous allez apprendre à implémenter des règles de gestion en utilisant les contraintes d'intégrité qu’elles soient référentielles ou sur domaine.

A la fin de ce chapitre, vous saurez :

• Définir les contraintes
• Créer des contraintes et les maintenir

SQL Server fait appel à des contraintes pour empêcher l'entrée de données incorrectes dans des tables. Il s’agit d’une approche non spécifique à SQL Server mais mis en œuvre par tous les SGBD conformes au modèle relationnel et à la norme SQL.

Vous pouvez utiliser des contraintes pour :

• Appliquer des règles au niveau d'une table chaque fois qu'une ligne est insérée, mise à jour ou supprimée dans cette table. La contrainte doit être satisfaite pour que l'opération réussisse.
• Empêcher la suppression d'un enregistrement s’il y a une dépendance avec un autre enregistrement de la table en relation.
• Eviter la suppression d'une table dont dépend une autre table. 
• Appliquer des règles aux valeurs insérées dans une colonne pour vérifier qu’elles sont conformes aux règles définies.

• Les contraintes contrôlent des règles de gestion au niveau d'une table.
• Les contraintes empêchent la suppression d'une table lorsqu'il existe des dépendances.
• Types de contraintes valides dans SQL :
  • IDENTITY
  • DEFAULT
  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK

Conventions Applicables aux Contraintes

Toutes les contraintes sont stockées dans le dictionnaire de données. Elles seront très faciles à manipuler si vous leur donnez un nom parlant.

Par défaut le système attribue un nom constitué à partir du nom des objets concernés et une référence ordinale.

Les noms de contraintes sont soumis aux conventions de dénomination des objets Sql Server.  Il est possible de définir les contraintes au moment de la création de la table ou par la suite. Il est préférable car plus simple de procéder à la mise en œuvre des contraintes dans un deuxième temps. 

Une contrainte peut être définie au niveau table ou colonne en fonction de sa nature.

Syntaxe :

CREATE TABLE table

                (column datatype [DEFAULT expr]

                     [column_constraint],

                     …

                     [table_constraint]);

Dans cette syntaxe :

      table                           nom de la table

      column                       nom de la colonne

      datatype                     type de données et longueur de la colonne

DEFAULT expr  valeur par défaut à utiliser si une valeur est omise dans l'ordre INSERT  column_constraint      contrainte d'intégrité incluse dans la définition de la colonne table_constraint contrainte d'intégrité incluse dans la définition de la table

Exemple:

CREATE TABLE Emp(

                     EmpNo  INT,

                     EmpName  VARCHAR(10),

                     CatEmp VARCHAR(10) default 'salarie',

                     …

                     deptno  INT NOT NULL,

                     CONSTRAINT Pk_Emp_Empno  PRIMARY KEY (EMPNO)); 

Syntaxe de création d’une contrainte :

      constraint_name             nom de la contrainte

      constraint_type               type de contrainte

• Contrainte au niveau colonne column [CONSTRAINT constraint_name] constraint_type,
• Contrainte au niveau table column,...

  [CONSTRAINT constraint_name] constraint_type

  (column, ...),

1.  

Spécifie la valeur fournie pour la colonne lorsque vous n'avez pas spécifié explicitement de valeur lors d'une insertion. Les définitions de valeurs par défaut peuvent s'appliquer à toutes les colonnes, excepté aux colonnes définies avec la propriété IDENTITY.

Seule une valeur constante, telle qu'une chaîne de caractères, une fonction système comme SYSTEM_USER() ou la valeur NULL, peut être utilisée comme valeur par défaut.  Vous pouvez affecter un nom de contrainte à une valeur DEFAULT. 

Exemple:

CREATE TABLE emp( empno  INT IDENTITY(5,3), ename  VARCHAR(10) 

CONSTRAINT df_name DEFAULT 'xx',

deptno  INT NOT NULL,

CONSTRAINT Pk_emp_empno 

PRIMARY KEY (EMPNO))

1.  

NULL et NOT NULL : Mots clés déterminant si les valeurs NULL sont permises ou non dans une colonne. 

La contrainte NOT NULL interdit la présence de valeurs NULL dans la colonne à laquelle elle s'applique. 

Par défaut, les colonnes qui ne sont pas associées à la contrainte NOT NULL peuvent contenir des valeurs NULL. 

1.  

Une contrainte PRIMARY KEY crée une clé primaire pour la table. Une seule clé primaire peut être créée par table. 

Contrainte assurant l'intégrité de l'entité d'une colonne ou de plusieurs colonnes données au moyen d'un seul index. Elle établit une règle d'unicité de la colonne ou d'une combinaison de colonnes et garantit qu'aucune colonne faisant partie de la clé primaire ne contient de valeur NULL. 

Exemple1:

 

                                                                                

Exemple2:

 

1. 1.  

IDENTITY (propriété)

Crée une colonne d'identité dans une table. Cette propriété est utilisée avec les instructions. Permet d’incrémenter automatiquement la valeur des clés primaires par incrémentation.

IDENTITY [ ( seed , increment ) ]

Seed: Valeur utilisée pour la toute première ligne chargée dans la table.

Increment : Valeur d'incrément ajoutée à la valeur d'identité de la ligne précédemment chargée.

Vous devez spécifier à la fois la valeur initiale et l'incrément, ou aucune des deux. Si vous n'en spécifiez aucune, la valeur par défaut est (1,1).

Cette propriété est associée à la colonne lors de la création de la table :

[IdContact] [int] IDENTITY (1, 1)

[IdCostumer] [int] IDENTITY (7, 2)

1.  

Une contrainte d'intégrité de type clé UNIQUE exige que chaque valeur dans une colonne ou dans un ensemble de colonnes soit unique, c'est-à-dire qu'elle n'existe pas dans plusieurs lignes pour la colonne ou l'ensemble de colonnes spécifiés.  Une table peut comprendre plusieurs contraintes UNIQUE.

La contrainte FOREIGN KEY, ou contrainte d'intégrité référentielle, désigne une colonne ou une combinaison de colonnes comme étant une clé étrangère et établit une relation avec une clé primaire ou une clé unique de la même table ou d'une table différente.

Exemple :

FOREIGN KEY(DEPTNO) REFERENCES DEPT (DEPTNO)

 Dans l'exemple ci-dessus, la colonne DEPTNO a été définie comme clé étrangère dans la table

EMP (table dépendante ou enfant) ; elle fait référence à la colonne DEPTNO de la table DEPT (table de référence ou parent). 

Une valeur de clé étrangère doit obligatoirement correspondre à une valeur existante de la table maître ou être NULL.

Les clés étrangères sont basées sur des valeurs de données et sont des pointeurs purement logiques et non physiques.

Les contraintes FOREIGN KEY peuvent être définies au niveau table ou colonne. Mais une clé étrangère composée se crée au moyen de la définition de niveau table.

La clé étrangère est définie dans la table détail et la table contenant la colonne référencée est la table maître.

Pour définir la clé étrangère, on utilise une combinaison des mots-clés suivants : 

• FOREIGN KEY définit une colonne de la table détail dans une contrainte de niveau table.
• REFERENCES identifie la table et la colonne dans la table maître.

ON DELETE (CASCADE, SET NULL, SET DEFAULT, NO ACTION)

• Permet de spécifier l’action à réaliser en cas de suppression d'une ligne possédant une clé référencée par des clés étrangères dans des lignes d'autres tables,

ON UPDATE (CASCADE, SET NULL, SET DEFAULT, NO ACTION)

• Permet de spécifier l’action à réaliser en cas mise à jour, dans une ligne, d'une valeur de clé référencée par des clés étrangères dans des lignes d'autres tables.

CASCADE

• Toutes les lignes contenant ces clés étrangères sont également supprimées ou mises à jour et remplacées par la nouvelle valeur spécifiée pour la clé.

SET NULL

• Remplace les valeurs par NULL

SET DEFAULT

• Les valeurs sont remplacées par la valeur par défaut, s’elle est définie sinon elle est remplacée par NULL.

NO ACTION

• Ne rien faire

Exemple:

 

1.  

La contrainte CHECK définit une condition que chaque ligne doit obligatoirement satisfaire. La condition peut utiliser les mêmes constructions que les conditions d'une requête. 

• Une colonne peut posséder un nombre illimité de contraintes CHECK et la condition peut inclure plusieurs expressions logiques combinées par AND et OR. S'il existe plusieurs contraintes CHECK pour une même colonne, elles sont validées dans l'ordre de leur création. 
• La condition de recherche doit correspondre à une expression booléenne et ne peut pas faire référence à une autre table. 
• Une contrainte CHECK de niveau colonne ne peut faire référence qu'à la colonne contenant la contrainte, et une contrainte CHECK de niveau table ne peut faire référence qu'aux colonnes d'une même table. 
• Les contraintes CHECK et les règles servent toutes les deux à valider les données lors des instructions INSERT et UPDATE. 
• Quand il existe une règle et une ou plusieurs contraintes CHECK pour une colonne, toutes les restrictions sont évaluées. 

Exemples :

Check (state IN ('NY',’YO’,’KA’))

Ou, pour s'assurer que les utilisateurs ne spécifient que des valeurs à 5 chiffres dans la colonne Code Postal, tapez : 

Check (CodePostal LIKE '[0-9] [0-9] [0-9] [0-9] [0-9]')

1.  

Vous pouvez ajouter des contraintes dans une table existante en utilisant l'ordre ALTER TABLE avec la clause ADD.

Syntaxe :

      ALTER TABLE      table

  ADD [CONSTRAINT constraint] type (column); Où :

            table                     nom de la table

            constraint             nom de la contrainte

            type                     type de contrainte

            column                 nom de la colonne concernée par la contrainte

  

• Vous pouvez ajouter ou supprimer une contrainte, mais pas la modifier • Vous pouvez activer ou désactiver des contraintes

Exemples1 :

ALTER TABLE     emp

      ADD CONSTRAINT       fk_emp_mgr 

FOREIGN KEY(mgr) REFERENCES emp(empno);

Ajout d'une Contrainte

L'exemple ci-dessus crée une contrainte FOREIGN KEY dans la table EMP. Cette contrainte vérifie qu'un manager existe en tant qu' « employé » valide dans la table EMP.

Ajouter une contrainte FOREIGN KEY à la table EMP précisant qu'un manager doit déjà exister dans la table EMP en tant qu'employé valide. 

Exemples2 : (cas bibliothèque)

 

1.  

Si vous souhaitez supprimer une contrainte, vous pouvez en retrouver le nom dans les vues du dictionnaire de données USER_CONSTRAINTS et USER_CONS_COLUMNS. 

Utilisez ensuite l'ordre ALTER TABLE avec la clause DROP. L'option CASCADE de la clause DROP provoque également la suppression de toutes les contraintes associées.

Syntaxe

      ALTER TABLE     table

 DROP PRIMARY KEY | UNIQUE (column) |

       CONSTRAINT   constraint  [CASCADE]; où :       table  représente le nom de la table

               column        représente le nom de la colonne concernée par la contrainte            constraint    représente le nom de la contrainte

Lorsque vous supprimez une contrainte d'intégrité, elle n'est plus contrôlée par SQL Server et n’existe plus dans le dictionnaire de données.

Supprimer de la table EMP la contrainte concernant le manager :

      ALTER TABLE     emp

DROP CONSTRAINT fk_emp_mgr;

Supprimer la contrainte PRIMARY KEY de la table DEPT, ainsi que la contrainte FOREIGN KEY associée définie sur la colonne EMP.DEPTNO. 

ALTER TABLE dept

DROP PRIMARY KEY CASCADE;

1.  

Uniquement valable avec les contraintes CHECK ou FOREIGN KEY

{ CHECK | NOCHECK} CONSTRAINT

Spécifie si constraint_name est activé ou désactivé.  

Si la contrainte est désactivée, les insertions et mises à jour ultérieures ne sont pas validées par rapport aux conditions de la contrainte. Cette option peut uniquement être utilisée avec les contraintes FOREIGN KEY et CHECK. 

ALL 

Spécifie que toutes les contraintes sont désactivées à l'aide de l'option NOCHECK ou activées à l'aide de l'option CHECK. 

Il est intéressant de pouvoir désactiver une contrainte sans la supprimer (ce qui évite d'avoir à la recréer) en utilisant l'ordre ALTER TABLE 

 

1.  

Pour afficher les contraintes définies sur les colonnes d’une table, exécutez la procédure stockée Sp_HelpConstraint avec en paramètre le nom de la table. 

1. Créer la structure de la base de données étude de cas : “Compagnie de transport”
   1. Créer les structures des tables de la base de données (colonnes, types, tailles) avec précision des contraintes d’intégrités référentielles ou sur domaine.
   2. Créer une contrainte Ville départ et ville d’arrivée champ obligatoire dans la table voyage
   3. Créer une contrainte tarif par défaut =0 et toujours >=0.
   4. Créer une contrainte qui impose la saisie de la colonne matricule véhicule sous la forme (00-A000000) deux chiffres ‘–‘ une lettre ‘–‘ six chiffres
   5. Créer une contrainte qui impose la saisie de la colonne type véhicule sous la forme (Bus, Minibus)
   6. Pour la contrainte d’intégrité référentielle clé étrangère n° voyage appliquer la règle sur suppression ‘set null’ et en cas de mise à jour en cascade 7. Créer le schéma de la base de données
2. Créer la structure de la base de données étude de cas : “ Séminaire de formation OFPPT ”
   1. Créer les structures des tables de la base de données (colonnes, types, tailles) avec précision des contraintes d’intégrités référentielles ou sur domaine.
   2. Créer une contrainte qui impose la saisie de la colonne matricule formateur sous la forme (000AA-000000) trois chiffres ‘–‘ deux lettres ‘–‘ six chiffres
   3. Créer une contrainte qui impose la saisie de la colonne code région sous la forme (AA00) deux lettres suivies de deux chiffres
   4. Pour la contrainte d’intégrité référentielle clé étrangère code formation appliquer la règle en cascade pour la suppression et la mise à jour 
   5. Créer le schéma de la base de données
3. Ajouter les contraintes d’intégrités référentielles et sur domaines aux structures des bases de données des études de cas : “Bibliothéque et Clinique ”