Permet de préciser le groupe de fichiers destination pour les données de type texte et image.
Il est possible de créer 2 milliards de tables par base de données. Le nombre maximal de
colonnes par table est de 1024. La longueur maximale d’une ligne est de 8060 octets (sans
compter les données texte ou image).
Revenons à notre cas Papyrus. Cliquez droit sur votre base de données puis « Nouvelle
requête… ».
Saisissons notre code Transact SQL suivant :
create table FOURNISSEUR (
NUMFOU int not null,
NOMFOU varchar(30) not null,
RUEFOU varchar(30) not null,
POSFOU varchar(5) not null,
VILFOU varchar(30) not null,
CONFOU varchar(15) not null,
SATISF smallint not null,
constraint PK_FOURNISSEUR primary key nonclustered (NUMFOU)
)
go
Pour exécuter votre requête, cliquez sur le bouton
pour lancer la requête. Si votre
requête c’est bien exécuté, un message : « Commande(s) réussie(s) » s’affiche alors.
46
Une contrainte d'intégrité est une clause permettant de contraindre la modification de tables,
faite par l'intermédiaire de requêtes d'utilisateurs, afin que les données saisies dans la base
soient conformes aux données attendues. Ces contraintes doivent être exprimées dès la
création de la table grâce aux mots clés suivants :
• CONSTRAINT
• DEFAULT
• NOT NULL
• UNIQUE
• CHECK
• CONSTRAINT
• DEFAULT
• NOT NULL
• UNIQUE
• CHECK
Le langage SQL permet de définir une valeur par défaut lorsqu'un champ de la base n'est pas
renseigné grâce à la clause DEFAULT. Cela permet notamment de faciliter la création de
tables, ainsi que de garantir qu'un champ ne sera pas vide.
La clause DEFAULT doit être suivie par la valeur à affecter. Cette valeur peut être un des
types suivants : Constante numérique, constante alphanumérique (chaîne de caractères), le
mot clé USER (nom de l'utilisateur), le mot clé NULL, le mot clé CURRENT_DATE (date de
saisie), le mot clé CURRENT_TIME (heure de saisie), le mot clé CURRENT_TIMESTAMP
(date et heure de saisie).
Le mot clé NOT NULL permet de spécifier qu'un champ doit être saisi, c'est-à-dire que le
SGBD refusera d'insérer des tuples dont un champ comportant la clause NOT NULL n'est pas
renseigné.
Il est possible de faire un test sur un champ grâce à la clause CHECK() comportant une
condition logique portant sur une valeur entre les parenthèses. Si la valeur saisie est
différente de NULL, le SGBD va effectuer un test grâce à la condition logique. Celui-ci peut
éventuellement être une condition avec des ordres SELECT...
La clause UNIQUE permet de vérifier que la valeur saisie pour un champ n'existe pas déjà
dans la table. Cela permet de garantir que toutes les valeurs d'une colonne d'une table seront
différentes.
CREATE TABLE MATABLE1
(COLONNE1 int UNIQUE)
La clause IDENTITY peut être affectée à une colonne par table, de type numérique entier.
Elle permet d’incrémenter les valeurs d’une colonne, ligne après ligne. Par défaut, la
contrainte IDENTITY part de 1, et a un pas d’incrément de 1. Il est possible de changer la
valeur de départ et le pas d’incrément. Proposons un script qui crée une table, avec deux
colonnes, une de type IDENTITY et une avec un type char, et faisons plusieurs insertions
dans cette table.
-- Créez la table avec la contrainte IDENTITY
CREATE TABLE MATABLE
(COLONNE1 NUMERIC(18,0) IDENTITY,
COLONNE2 char(10))
47
-- Insertion multiple dans notre nouvelle table
INSERT INTO MATABLE
(COLONNE2)
VALUES
('Cours 1'),('Cours 2'),('Cours 3')
Remarquez que lorsque l’on insère des lignes dans une table comportant une colonne
IDENTITY, nous n’avons pas besoin de préciser la colonne et la valeur qu’elle prend en
argument, d’où son intérêt, d’automatiser la saisie des données. Vérifions maintenant le
résultat avec un simple SELECT :
On remarque bien que la colonne COLONNE1 s’est peuplée seule, grâce à la contrainte
IDENTITY. Il est bon de rappeler que nous n’avons droit qu’à une seule contrainte
IDENTITY par table.
Il est possible de donner un nom à une contrainte grâce au mot clé CONSTRAINT suivi du
nom que l'on donne à la contrainte, de telle manière à ce que le nom donné s'affiche en cas
de non-respect de l'intégrité, c'est-à-dire lorsque la clause que l'on a spécifiée n'est pas
validée.
Si la clause CONSTRAINT n'est pas spécifiée, un nom sera donné arbitrairement par le
SGBD. Toutefois, le nom donné par le SGBD risque fortement de ne pas être compréhensible,
et ne sera vraisemblablement pas compris lorsqu'il y aura une erreur d'intégrité. La
stipulation de cette clause est donc fortement conseillée.
Exemple : Voici un exemple permettant de voir la syntaxe d'une instruction de création de
tables avec contraintes :
CREATE TABLE clients(
Nom char(30) NOT NULL,
Prenom char(30) NOT NULL,
Age integer, check (age < 100),
Email char(50) NOT NULL, check (Email LIKE "%@%")
)
Grâce à SQL, il est possible de définir des clés, c'est-à-dire spécifier la (ou les) colonne(s)
dont la connaissance permet de désigner précisément un et un seul tuple (une ligne).
L'ensemble des colonnes faisant partie de la table en cours permettant de désigner de façon
unique un tuple est appelé clé primaire et se définit grâce à la clause PRIMARY KEY suivie
de la liste de colonnes, séparées par des virgules, entre parenthèses. Ces colonnes ne
peuvent alors plus prendre la valeur NULL et doivent être telles que deux lignes ne puissent
avoir simultanément la même combinaison de valeurs pour ces colonnes.
48
PRIMARY KEY (colonne1, colonne2, ...)
Lorsqu'une liste de colonnes de la table en cours de définition permet de définir la clé primaire
d'une table étrangère, on parle alors de clé étrangère, et on utilise la clause FOREIGN KEY
suivie de la liste de colonnes de la table en cours de définition, séparée par des virgules, entre
parenthèses, puis de la clause REFERENCES suivie du nom de la table étrangère et de la liste
de ses colonnes correspondantes, séparées par des virgules, entre parenthèses.
FOREIGN KEY (colonne1, colonne2, ...)
REFERENCES Nom_de_la_table_etrangere(colonne1,colonne2,...)
Trigger (gâchette): -- Garantie de l'intégrité référentielle
Les clés étrangères permettent de définir les colonnes d'une table garantissant la validité
d'une autre table. Ainsi, il existe des éléments (appelés triggers, ou en français gâchettes ou
déclencheurs) permettant de garantir l'ensemble de ces contraintes que l'on désigne par le
terme d'intégrité référentielle, c'est-à-dire notamment de s'assurer qu'un tuple utilisé à partir
d'une autre table existe réellement.
Ces triggers sont ON DELETE et ON UPDATE :
ON DELETE est suivi d'arguments entre accolades permettant de spécifier l'action à réaliser
en cas d'effacement d'une ligne de la table faisant partie de la clé étrangère :
CASCADE indique la suppression en cascade des lignes de la table étrangère dont les
clés étrangères correspondent aux clés primaires des lignes effacées.
RESTRICT indique une erreur en cas d'effacement d'une valeur correspondant à la
clé.
SET NULL place la valeur NULL dans la ligne de la table étrangère en cas
d'effacement d'une valeur correspondant à la clé.
SET DEFAULT place la valeur par défaut (qui suit ce paramètre) dans la ligne de la
table étrangère en cas d'effacement d'une valeur correspondant à la clé.
ON UPDATE est suivi d'arguments entre accolades permettant de spécifier l'action à réaliser
en cas de modification d'une ligne de la table faisant partie de la clé étrangère :
CASCADE indique la modification en cascade des lignes de la table étrangère dont les
clés primaires correspondent aux clés étrangères des lignes modifiées.
RESTRICT indique une erreur en cas de modification d'une valeur correspondant à la
clé.
SET NULL place la valeur NULL dans la ligne de la table étrangère en cas de
modification d'une valeur correspondant à la clé.
SET DEFAULT place la valeur par défaut (qui suit ce paramètre) dans la ligne de la
table étrangère en cas de modification d'une valeur correspondant à la clé.
49
Exemple : On créer une base test dans laquelle on exécute le script suivant. Ont créé deux
tables avec les contraintes suivantes.
USE Test
CREATE TABLE MATABLE1
(COLONNE1 int PRIMARY KEY)
CREATE TABLE MATABLE2
(COLONNE1 int CONSTRAINT FK_MATABLE2 FOREIGN KEY (COLONNE1)
REFERENCES MATABLE1 (COLONNE1)
ON DELETE CASCADE)
On enregistre les données suivantes :
Maintenant, on supprime la ligne 2 (11) de la table MATABLE1 et on actualise notre base de
données puis on affiche de nouveau les deux tables : on constate que la mise à jour a été
automatiquement faite dans la table 2.
Les assertions sont des expressions devant être satisfaites lors de la modification de données
pour que celles-ci puissent être réalisées. Ainsi, elles permettent de garantir l'intégrité des
données. Leur syntaxe est la suivante :
CREATE ASSERTION Nom_de_la_contrainte CHECK (expression_conditionnelle)
La condition à remplir peut (et est généralement) être effectuée grâce à une clause SELECT.
Pour notre base de données « Papyrus », si votre table contient une clé étrangère :
use Papyrus
create table VENDRE (
CODART char(4) not null,
NUMFOU int not null,
DELLIV smallint not null,
QTE1 smallint not null,
PRIX1 money not null,
QTE2 smallint not null,
PRIX2 money not null,
50
QTE3 smallint not null,
PRIX3 money null,
constraint PK_VENDRE primary key (CODART, NUMFOU),
constraint FK_VENDRE_VENDRE_PRODUIT foreign key (CODART)
references PRODUIT (CODART)
)
go
La clé primaire est le champ CODART ET NUMFOU. La contrainte FOREIGN KEY, basé sur la
colonne CODART référence le champ CODART de la table PRODUIT pour garantir que toute
vente est associée à un produit existant.
L'instruction USE vous permet de spécifier le fichier de base de données SQL Server à
interroger (spécifier la base de données concernée par la requête) lors de l'utilisation de SQL
Server Management Studio.
Si vous n’utilisez pas USE, assurez-vous lorsque vous effectué une requête que celle-ci
s’applique bien à la base de données sur laquelle vous travaillez.
NB : Pour faire des commentaires dans vos requêtes…
--Commentaires sur une seule ligne
/* Commentaires
sur
plusieurs
lignes */
La contrainte CHECK permet de vérifier, avant insertion ou mise à jour des données
contenues dans la colonne en question, que les données à insérer sont bien au format voulu,
ou encore qu’une valeur entrée dans la colonne pour un enregistrement appartiendra à un
domaine de valeurs particulier. Regardons maintenant la syntaxe de cette contrainte :
CREATE TABLE MATABLE1
(COLONNE1 int CHECK (expression_booleenne))
Il est possible d’ajouter l’option NOT FOR REPLICATION après le mot clé CHECK, afin de
spécifier qu’il faut empêcher l’application de la contrainte dans un cas de réplication.
