SQL SERVER integrité des données

Implémentation de l’intégrité des données Mme Aissat

III- Implémentation de l’intégrité des données

Objectif pédagogique :

A la fin du chapitre, le stagiaire sera capable de : Décrire les différents types de données ; Définir et utiliser les contraintes DEFAULT, CHECK, PRIMARY KEY, UNIQUE et FOREIGN

KEY ; Désactiver la validation des contraintes ;

1


1- Introduction

L’application de l'intégrité des données garantit la qualité des données stockées dans la base. Par exemple, si un employé est entré avec un matricule 123, la base de données ne doit pas autoriser qu'un

autre employé ait le même matricule. Si une colonne employee_cat est destinée à accueillir des valeurs comprises dans une fourchette

de 1 à 5, la base de données ne doit pas autoriser une valeur de 6. Si la table contient une colonne dept_id qui stocke le numéro de service de l'employé, la base

de données ne doit accepter que des valeurs correspondant bien aux identificateurs de service de la société.

Lors de la planification des tables, deux étapes importantes consistent d'une part à identifier les valeurs valides pour une colonne et d'autre part à décider de la façon d'appliquer l'intégrité des données dans cette colonne.

2- Types d’Intégrité des donnéesSQL SERVER implémente les types d’intégrité suivants :

a- Intégrité de domaine :L’intégrité de domaine (ou de colonne) spécifie l’ensemble de valeur permises pour une

colonne. Elle est implémentée à l’aide des contraintes DEFAULT, CHEK et REFERENTIAL.b- Intégrité d’entité :

L’intégrité d’entité (ou de table) implique que toutes les lignes d’une table aient un identificateur unique, appelé valeur de clé primaire. Elle est implémentée à l’aide des contraintes PRIMARY KEY et UNIQUE.

c- Intégrité référentielle :L’intégrité référentielle préserve les relations entre les tables lors d’un ajout ou une

suppression. Elle est implémentée à l’aide des contraintes FOREIGN KEY et CHECK.

3- Définition des contraintes :Selon l’endroit où les contraintes sont définies, SQL SERVER distingue :

Les contraintes déclaratives : qui sont définies lors de la création des tables et des colonnes.

Les contraintes procédurales : qui sont implémentée en utilisant des scripts, des procédures stockées ou des déclencheurs.

3-1- Contraints déclaratives :Elles sont définies soit

Lors de la définition d’une table avec CREATE TABLE Lors de la modification d’une table déjà définie avec ALTER TABLE. Au niveau colonne c-à-d lors de la définition de la colonne en lui donnant un nom ou sans

lui donner un nom. Au niveau table c-à-d lors de la définition de la table après avoir définis toutes les

colonnes.

3-1-1- Contrainte DEFAULT :

2


La contrainte DEFAULT permet de donner des valeurs par défaut aux colonnes par le système et cela lors d’une insertion de données. Une seule contrainte DEFAULT est définie pour une colonne

A l’aide du transact-SQLSyntaxe partielle : [CONSTRAINT nom_de_la_contrainte] DEFAULT expression_constante

a- Lors de la création :Exemple1 : définition de la contrainte niveau colonne

CREATE TABLE employes(Idempl int Not null,Nom nvarchar(15) Not null,Prenom nvarchar(20) Not null,Adresse ntext null,Tel nvarchar(14) CONSTRAINT DF_tel DEFAULT ’00.00.00.00.00’ )

Dans cet exemple nous avons donné un nom pour la contrainte DF_tel cela permet de l’utiliser ultérieurement pour la désactiver par exemple.

CREATE TABLE employes(Idempl int Not null,Nom nvarchar(15) Not null,Prenom nvarchar(20) Not null,Adresse ntext null,Tel nvarchar(14) DEFAULT ’00.00.00.00.00’ )

La contrainte ici n’a pas de nom.b- Après la création de la table

Exemple 2 : ALTER TABLE employes ADD CONSTRAINT DF_tel DEFAULT ’00.00.00.00.00’ FOR (tel)

A l’aide de Entreprise Manager

Dans le schéma de base de données, cliquez sur Tables

2. Sélectionnez la table dont vous souhaitez modifier les propriétés des colonnes.

3. Cliquez avec le bouton droit sur la table et sélectionnez Modifier la table dans le menu contextuel.

4. Ouvrez le Concepteur de table , sélectionnez la colonne dont vous voulez apporter une valeur par défaut.

5. Sur l'onglet Colonne , donnez une valeur dans la zone Valeur par défaut

6. Enregistrer la table et fermer la fenêtre.

Dans la syntaxe expression_constante peut être :

Constante numérique

constante alphanumérique (chaîne de caractères) le mot clé USER qui renvoie le nom de l'utilisateur sous forme de chaîne de caractères.

Dans cet exemple une colonne utilisateur est ajouter à la table article est qui est rempli par le nom de l’utilisateur actuel de l’utilisateur actuel de la table.

3


Alter table article

add utilisateur varchar(30) DEFAULT user Le même résultat peut être eu avec les valeurs système CURRENT_USER et USER_NAME() à la place de USER.

Exemple 3 :

L'exemple suivant crée une table utilisant CURRENT_USER en tant que contrainte DEFAULT pour la colonne command_person d'une ligne ventes.

CREATE TABLE commande( comd_id int IDENTITY(1000, 1) NOT NULL, client_id int NOT NULL, cmd_date datetime NOT NULL DEFAULT GETDATE(), cmd_mont money NOT NULL, command_person char(30) NOT NULL DEFAULT CURRENT_USER)

La colonne cmd_date est définie avec la contrainte DEFAULT Getdate() qui est une fonction qui fournie la date et l’heure système actuelle.Remarques :

Pour modifier une définition DEFAULT à l'aide de Transact-SQL, vous devez commencer par supprimer la définition DEFAULT existante, puis la recréer en précisant sa nouvelle définition.

Si vous ajoutez une définition DEFAULT à une colonne existante d'une table, Microsoft® SQL Server™ 2000 applique par défaut la nouvelle valeur par défaut aux nouvelles lignes de données ajoutées à la table seulement. Les données existantes insérées à l'aide de la définition DEFAULT précédente ne sont pas affectées.

Si vous supprimez une définition DEFAULT, SQL Server insère une valeur NULL plutôt que la valeur par défaut, au cas où aucune valeur n'est ajoutée à la colonne pour les nouvelles lignes. Toutefois, aucune modification n'est apportée aux données existantes de la table.

3-1-1- Contrainte CHECK :

La contrainte CHECK limite les valeurs pouvant être entrées par les utilisateurs dans une colonne données.Exemple : la colonne NOTE d’un stagiaire par exemple doit prendre des valeurs comprises entre 0 et 20.

Syntaxe simplifiée : [CONSTRAINT nom_de_la_contrainte]CHECK (expression_logique)

3-1-1-1- Implémentation de la contrainte CHECKUne contrainte CHECK peut être :

créée lors de la création de la table (niveau colonne ou niveau table) ajoutée à une table existante, modifiée ou supprimée si elle existe déjà.

a- Lors de la création de table :a-1- Niveau colonne :

4


dans cette table le salaire d’un employé est compris entre 10000 et 25000 DA par exemple.Avec une contrainte est nommée : CREATE TABLE Employes (IdEmpl int IDENTITY(100,1) NOT NULL, Nom nvarchar(15), Prenom nvarchar(15), Adresse ntext NULL, Sit_fam char(1) NULL, Salaire money NULL CONSTRAINT CK_salaire CHECK (salaire >10000 and salaire <25000))Avec une contraintequi n’est pas nommée : CREATE TABLE Employes (IdEmpl int IDENTITY(100,1) NOT NULL, Nom nvarchar(15), Prenom nvarchar(15), Adresse ntext NULL, Sit_fam char(1) NULL, Salaire money NULL CHECK (salaire >10000 and salaire <25000))

a-2- Niveau table :

CREATE TABLE Employes (IdEmpl int IDENTITY(100,1) NOT NULL, Nom nvarchar(15), Prenom nvarchar(15), Adresse ntext NULL, Sit_fam char(1) NULL, Salaire money NULL, CONSTRAINTE CK_salaire CHECK (salaire >10000 and salaire <25000), CONSTRAINTE CK_Sit_fam CHECK (sit_fam IN (‘D’,’C’,’M’,’V’))

b- Ajoutée à une table déjà existante :Syntaxe partielle : ALTER TABLE table

ADD [CONSTRAINT contrainte] CHECK (expression_logique)

ALTER TABLE avoir_note ADD CONSTRAINTE CK_Note CHECK (note >=0 and Note <=20)

Ou :ALTER TABLE avoir_note

ADD CHECK (note >=0 and Note <=20)

Ouvrir le Concepteur de table (Diagramme) pour la table qui contiendra la contrainte, cliquez avec le bouton droit sur le Concepteur de table et sélectionnez Contraintes CHECK dans le menu contextuel.

5


2. Cliquez sur Nouveau. La zone Contrainte sélectionnée affiche le nom attribué par le système à la nouvelle contrainte. Ce nom commence par « CK_ » suivi du nom de la table.

3. Dans la zone Expression de contrainte, tapez les instructions SQL permettant de définir la contrainte CHECK.

4. Si vous souhaitez renommer la contrainte, tapez un autre nom dans le champ Nom de la contrainte.

5. Activez ou désactivez les cases à cocher pour contrôler à quel moment la contrainte doit s'appliquer .

ExemplesAjouter une colonne catégorie a article

ALTER TABLE articles ADD cat char(4) null

Ajouter une contrainte check pour la colonne cat qui est codifié comme suit 99AA tel que 9 représente une position numérique et A une position caractère alphabétique.

On utilise la clause LIKE comme suit

ALTER TABLE articles ADD CONSTAINT ck_cat CHECK (cat LIKE '[0-9][0-9][A-Z][A-Z]')

Cette contrainte garantie que seules les valeurs composées de deux chiffres suivis de deux caractères alphabétique sont autorisés.

LIKEDétermine si une chaîne de caractères donnés correspond à une chaîne spécifiée.[0-9] la position est un chiffre de 0 à 9[A-Z] la position est un caractère alphabétique de A à Z.[ abcd] la position est égale à soit a ou b ou c ou d une seule valeur parmi la liste.

6


Supposons que la table Auteurs comporte une colonne zip nécessitant une chaîne de caractères de 5 chiffres. Voici un exemple d'expression de contrainte autorisant uniquement les nombres à cinq chiffres dans cette colonne :

zip LIKE '[0-9][0-9][0-9][0-9][0-9]'

La cote d’un ouvrage est codifié comme suit ‘Inf999’ tous les codes commencent par Inf suivi de 3 chiffres. L’expression de la contrainte respectant ce code est :

Cote LIKE ‘ Inf[0-9] [0-9] [0-9]’

Supposons que la table élève comporte une colonne numéro qui est codifié comme suit :X99 tel que X peut être I pour un élève interne E pour un élève Externe et D pour un élève demi-pension.

L’expression de la contrainte respectant ce code est : Numero LIKE ‘[IED] [0-9][0-9]’

Remarque- 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.

Suppression d'une contrainte CHECK

- Supprimez la contrainte CHECK lorsque vous ne souhaitez plus limiter les valeurs autorisées

dans la colonne ou dans les colonnes spécifiées dans l'expression de contrainte.

A l'aide de Transact-SQL Syntaxe partielle : ALTER TABLE table DROP [ CONSTRAINT ] constraint_name A l'aide de l'Entreprise Manager

1. Ouvrez le Concepteur de table pour la table contenant la contrainte, cliquez avec le bouton droit sur le Concepteur de table et sélectionnez Propriétés dans le menu contextuel.

2. Cliquez sur l'onglet Contraintes CHECK.

3. Dans la liste Contrainte sélectionnée, sélectionnez la contrainte.

4. Cliquez sur Supprimer. Les opérations activer et désactiver une contrainte seront vues ultérieurement dans les instructions INSERT et

UPDATE.

3- La contrainte PRIMERY KEYAvant d’aborder la notion de clé primaire , nous allons voir la notion d’index qui est intimement liée a la clé

primaire.

3-1- C’est quoi un INDEX ?Les index des bases de données sont similaires aux index que l'on trouve dans les livres. L'index d'un livre vous

permet de rechercher rapidement des informations sans lire la totalité de l'ouvrage. Celui d'une base de données permet quant à lui au programme de gestion de base de données de rechercher des données dans une table sans analyser la table tout entière.

L'index d'un livre est une liste de mots accompagnés du numéro de page où ils se trouvent, L'index d'une base de données est une liste des valeurs d'une table accompagnées des emplacements de stockage des lignes où les valeurs se trouvent.

7

Table employesIDempl Nom Adresse Date_RecF234 Ben ali TO ……A344 Ouldbrahim Oran ……B345 ……………. ………………. …………………


Lorsque SQL Server exécute une instruction pour rechercher des données dans la table employes en fonction d'une valeur Idempl spécifiée, il reconnaît l'index de la colonne Idempl et l'utilise pour la recherche.

En l'absence de cet index, SQL Server effectue une analyse complète de la table, commençant au début de la table et parcourant chaque ligne, en recherchant la valeur Idempl spécifiée.

Un index est accélérateur d’accès.

Il existe deux types d'index dans SQL Server : "Clustered" indique que physiquement les lignes de la table sont rangées selon l'ordre indiqué

dans l'index.

Un index 'nonclustered' n'influe pas l'ordre physique de rangement : il s'agit d'un ordre logique.

3-2- La contrainte PRIMARY KEY

Elle permet d'indiquer dans une table une colonne ou un ensemble de colonnes qui doit contenir des valeurs identifiant de façon unique une ligne de la table.

Cette colonne doit être définie impérativement NOT NULL. Une table ne peut posséder qu'une seule clé primaire donc une seule contrainte PRIMARY par table. Un index est crée sur les colonnes spécifiées

3-3- Création d’une contrainte PRIMARY KEY

a- A l’aide de TRansact-SQL :Syntaxe simplifiée : [CONSTRAINT] nom_contrainte

PRIMARY KEY [CLUSTERED|NOCLUSTERED] Lors de création de tableNiveau colonne:

CREATE TABLE Employee ( EmplID Int NOT NULL PRIMARY KEY ,

Nom nvarchar(20) NOT NULL, Prenom varchar(15) NOT NULL)

La contrainte n’a pas de nom.

Niveau table : CREATE TABLE Employee ( EmplID Int NOT NULL ,

Nom nvarchar(20) NOT NULL, Prenom varchar(15) NOT NULL),

8

.A344B345

.

.F234

.

Index sur IDempl


CONSTRAINT PK_EmplID PRIMARY KEY (EmplID))

Après la création de la table

ALTER TABLE Employee

ADD CONSTRAINT PK_EmplID PRIMARY KEY (EmplID)

A l'aide de l'Entreprise manager

1. Dans votre schéma de base de données ou le Concepteur de table, cliquez sur le sélecteur de ligne correspondant à la colonne que vous souhaitez définir comme clé primaire pour votre table. Pour sélectionner plusieurs colonnes à la fois, cliquez sur chaque sélecteur de ligne tout en maintenant la touche CTRL enfoncée.

2. Cliquez avec le bouton droit sur le sélecteur de ligne correspondant à la colonne et sélectionnez Définir la clé primaire. Le programme crée automatiquement un index de clé primaire (« PK_ » suivi du nom de la table) ; vous pouvez le retrouver dans l'onglet Index/Clés de la boîte de dialogue Pages de propriétés.

Suppression d'une contrainte de clé primaire

Supprimez une contrainte de clé primaire si vous ne voulez plus contraindre les utilisateurs à entrer

des valeurs uniques dans une colonne ou une combinaison de colonnes.

A l’aide du transact SQL

ALTER TABLE table DROP CONSTRAINT constraint_name

Exemple :

ALTER TABLE Employee

DROP CONSTRAINT PK_EmplID

A l'aide de l'Entreprise manager

Dans le schéma de base de données ou le Concepteur de table, sélectionnez les colonnes de clé primaire

de la table pour laquelle vous souhaitez supprimer la contrainte de clé primaire.

1. Cliquez avec le bouton droit sur le sélecteur de ligne correspondant à la colonne et sélectionnez Définir la clé primaire.

-ou-

2. Dans votre schéma de base de données, sélectionnez la table pour laquelle vous souhaitez supprimer la contrainte de clé primaire.

3. Ouvrez le Concepteur de table pour la table contenant la contrainte, cliquez avec le bouton droit sur le Concepteur de table et sélectionnez Index/Clés.

4. Dans la liste Index sélectionné, sélectionnez l'index de clé primaire.

9


5. Cliquez sur Supprimer.

Exercice d’application :Définir les clés primaires des tables de la base de données Biblio

Pour la table Auteur en utilisant Entreprise Manager Pour les tables Livres ; Abonne, Exemplaire avec le Transact-SQl dans l’analyseur de requêtes.

10

Documents

SQL SERVER integrité des données