Chapitre 3
SQL (DDL)
1 Le langage de définition de données SQL
Cette section présente le langage de définition de données (LDD) qui permet de spécifier le schéma
d’une base de données relationnelle. Ce langage correspond à une partie de la norme SQL
(structured query language), l’autre partie étant relative à la manipulation des données (LMD).
La définition d’un schéma logique comprend essentiellement deux parties : d’une part la description
des tables et de leur contenu, d’autre part les contraintes qui portent sur les données de la base.
Il existe plusieurs versions de SQL. Le plus ancien standard date de 1989. Il a été révisé de manière
importante en 1992 : la norme résultant de cette révision est SQL-92 ou SQL2.
1. 1 Types SQL
La norme SQL ANSI propose un ensemble de types qui sont donnés dans le tableau suivant :
4. 2 Création des tables
La commande principale est CREATE TABLE. Voici la commande de création d’une table
Internaute.
CREATE TABLE Internaute (email VARCHAR (50) NOT NULL,
nom VARCHAR (20) NOT NULL,
prenom VARCHAR (20),
motDePasse VARCHAR (60) NOT NULL,
anneeNaiss DECIMAL (4))
La syntaxe se comprend aisément. La seule difficulté est de choisir correctement le type de chaque
attribut. Le NOT NULL dans la création de table Internaute indique que l’attribut correspondant
doit toujours avoir une valeur. Quand on parle de valeur NULL en SQL2, il s’agit en fait d’une
absence de valeur. En conséquence : on ne peut pas faire d’opération incluant un NULL; on ne peut
pas faire de comparaison avec un NULL.
Dans l’exemple le SGBD rejettera alors toute tentative d’insérer une ligne dans Internaute sans
donner de mot de passe. Une autre manière de forcer un attribut à toujours prendre une valeur est de
spécifier une valeur pardéfaut avec l’option DEFAULT.
CREATE TABLE Cinéma (nom VARCHAR (50) NOT NULL,
adresse VARCHAR (50) DEFAULT ’Inconnue’)
Quand on insérera une ligne dans la table Cinéma sans indiquer d’adresse, le système affectera
automatiquement la valeur ’Inconnu’ à cet attribut.
1
Bases de données avancées 2005-2006
4.3 Contraintes
La création d’une table telle qu’on l’a vue précédemment est extrêmement sommaire car elle
n’indique que le contenu de la table sans spécifier les contraintes que doit respecter ce contenu. Or
il y a toujours des contraintes et il est indispensable de les inclure dans le schéma pour assurer
l’intégrité de la base.
Voici les règles (ou contraintes d’intégrité) que l’on peut demander au système de garantir :
1. Un attribut doit toujours avoir une valeur. C’est la contrainte NOT NULL vue précédemment.
2. Un attribut (ou un ensemble d’attributs) constitue(nt) la clé de la relation.
3. Un attribut dans une table est lié à la clé primaire d’une autre table (intégrité référentielle).
4. La valeur d’un attribut doit être unique au sein de la relation.
5. Enfin toute règle s’appliquant à la valeur d’un attribut (min et max par exemple).
Les contraintes sur les clés doivent être systématiquement spécifiées. La dernière (clause CHECK)
s’appuie en grande partie sur la connaissance du langage d’interrogation de SQL et sera vue
ultérieurement.
Clés d’une table
Une clé est un attribut (ou un ensemble d’attributs) qui identifie(nt) de manière unique un tuple
d’une relation. Il peut y avoir plusieurs clés mais l’une d’entre elles doit être choisie comme clé
primaire. La clé primaire est spécifiée avec l’option PRIMARY KEY.
CREATE TABLE Internaute (email VARCHAR (50) NOT NULL,
nom VARCHAR (20) NOT NULL,
prenom VARCHAR (20),
motDePasse VARCHAR (60) NOT NULL,
anneeNaiss INTEGER,
PRIMARY KEY (email))
Il devrait toujours y avoir une PRIMARY KEY dans une table pour ne pas risquer d’insérer deux
lignes strictement identiques. Une clé peut être constituée de plusieurs attributs :
CREATE TABLE Notation (idFilm INTEGER NOT NULL,
email VARCHAR (50) NOT NULL,
note INTEGER DEFAULT 0,
PRIMARY KEY (titre, email))
Tous les attributs figurant dans une clé doivent être déclarés NOT NULL. On peut également
spécifier que la valeur d’un attribut est unique pour l’ensemble de la colonne. Cela permet
d’indiquer des clés secondaires. On peut par exemple indiquer que deux artistes ne peuvent avoir
les mêmes nom et prénom avec l’option UNIQUE.
CREATE TABLE Artiste(id INTEGER NOT NULL,
nom VARCHAR (30) NOT NULL,
prenom VARCHAR (30) NOT NULL,
anneeNaiss INTEGER,
PRIMARY KEY (id),
UNIQUE (nom, prenom));
La clause UNIQUE ne s’applique pas aux valeurs NULL.
Clés étrangères
La norme SQL ANSI permet d’indiquer quelles sont les clés étrangères dans une table, autrement
dit, quels sont les attributs qui font référence à une ligne dans une autre table. On peut spécifier les
2
Bases de données avancées 2005-2006
clés étrangères avec l’option FOREIGN KEY.
CREATE TABLE Film (idFilm INTEGER NOT NULL,
titre VARCHAR (50) NOT NULL,
annee INTEGER NOT NULL,
idMES INTEGER,
codePays INTEGER,
PRIMARY KEY (idFilm),
FOREIGN KEY (idMES) REFERENCES Artiste,
FOREIGN KEY (codePays) REFERENCES Pays);
La commande FOREIGN KEY (idMES) REFERENCES Artiste indique que idMES
référence la clé primaire de la table Artiste. Le SGBD vérifiera alors, pour toute modification
pouvant affecter le lien entre les deux tables, que la valeur de idMES correspond bien à une ligne
de Artiste. Il faut noter que l’attribut idMES n’est pas déclaré NOT NULL. Quand un attribut est à
NULL, la contrainte d’intégrité référentielle ne s’applique pas.
Que se passe-t-il quand la violation d’une contrainte d’intégrité est détectée par le système ? Par
défaut, la mise à jour est rejetée, mais il est possible de demander la répercussion de cette mise à
jour de manière à ce que la contrainte soit respectée. Les événements que l’on peut répercuter sont
la modification ou la destruction de la ligne référencée, et on les désigne par ON UPDATE et ON
DELETE respectivement. La répercussion elle-même consiste soit à mettre la clé étrangère à NULL
(option SET NULL), soit à appliquer la même opération aux lignes de l’entité composante (option
CASCADE). Voici comment on indique que la destruction d’un metteur en scène déclenche la mise
à NULL de la clé étrangère idMES pour tous les films qu’il a réalisé.
CREATE TABLE Film (titre VARCHAR (50) NOT NULL,
annee INTEGER NOT NULL,
idMES INTEGER,
codePays INTEGER,
PRIMARY KEY (titre),
FOREIGN KEY (idMES) REFERENCES Artiste
ON DELETE SET NULL,
FOREIGN KEY (codePays) REFERENCES Pays);
Dans le cas d’une entité faible, on décide en général de détruire le composant quand on détruit le
composé. Par exemple, quand on détruit un cinéma, on veut également détruire les salles ; quand on
modifie la clé d’un cinéma, on veut répercuter la modification sur ses salles.
CREATE TABLE Salle (nomCinema VARCHAR (30) NOT NULL,
no INTEGER NOT NULL,
capacite INTEGER,
PRIMAR KEY (nomCinema, no),
FOREIGN KEY (nomCinema) REFERENCES Cinema
ON DELETE CASCADE
ON UPDATE CASCADE
)
Il est important de noter que nomCinema fait partie de la clé et ne peut donc pas être NULL. On
ne pourrait donc pas spécifier ici ON DELETE SET NULL. La spécification des actions ON
DELETE et ON UPDATE simplifie considérablement la gestion de la base par la suite : on n’a
plus par exemple à se soucier de détruire les salles quand on détruit un cinéma.
Énumération des valeurs possibles avec CHECK
La norme SQL ANSI comprend une option CHECK (condition) pour exprimer des contraintes
portant soit sur un attribut, soit sur une ligne. La condition elle-même peut être toute expression
suivant la clause WHERE dans une requête SQL. Les contraintes les plus courantes sont celles
consistant à restreindre un attribut à un ensemble de valeurs.
3
Bases de données avancées 2005-2006
Voici un exemple simple qui restreint les valeurs possibles des attributs annee et genre dans la
table Film.
CREATE TABLE Film (titre VARCHAR (50) NOT NULL,annee INTEGER
CHECK (annee BETWEEN 1890 AND 2000) NOT NULL,
genre VARCHAR (10)
CHECK (genre IN (’Histoire’,’Western’,’Drame’)),
idMES INTEGER,
codePays INTEGER,
PRIMARY KEY (titre),
FOREIGN KEY (idMES) REFERENCES Artiste,
FOREIGN KEY (codePays) REFERENCES Pays);
Au moment d’une insertion dans la table Film, ou d’une modification de l’attribut genre, le
SGBD vérifie que la valeur insérée dans genre appartient à l’ensemble énuméré défini par la
clause CHECK.
4. 4 Modification du schéma
La création d’un schéma n’est qu’une première étape dans la vie d’une base de données. On est
toujours amené par la suite à créer de nouvelles tables, à ajouter des attributs ou à en modifier la
définition. La forme générale de la commande permettant de modifier une table est :
ALTER TABLE nomTable ACTION description
où ACTION peut être principalement ADD, MODIFY, DROP ou RENAME, et description est
la commande de modification associée à ACTION. La modification d’une table peut poser des
problèmes si elle est incompatible avec le contenu existant. Par exemple passer un attribut à NOT
NULL implique que cet attribut a déjà des valeurs pour toutes les lignes de la table.
Modification des attributs
Voici quelques exemples d’ajout et de modification d’attributs. On peut ajouter un attribut region
à la table Internaute avec la commande:
ALTER TABLE Internaute ADD region VARCHAR(10);
La taille de region étant certainement insuffisante, on peut l’agrandir avec MODIFY, et la
déclarer NOT NULL par la même occasion :
ALTER TABLE Internaute MODIFY region VARCHAR(30) NOT NULL;
Il est également possible de diminuer la taille d’une colonne, avec le risque d’une perte
d’information pour les données existantes. On peut même changer son type, pour passer par
exemple de VARCHAR à INTEGER, avec un résultat imprévisible. L’option ALTER TABLE
permet d’ajouter une valeur par défaut.
ALTER TABLE Internaute ALTER region SET DEFAULT ’PACA’;
Enfin on peut détruire un attribut avec DROP.
ALTER TABLE Internaute DROP region;
4
Bases de données avancées 2005-2006