Cours Bases de données
Chapitre 4 : LE LANGAGE DE DÉFINITION DE
DONNÉES(LDD)
Objectifs
Maîtriser les différentes commandes de définition de données. L’apprenant
doit pouvoir créer sa base de données à partir d’un modèle relationnel tout
en prenant en considérations les contraintes d’intégrité nécessaires.
Éléments de contenu
Introduction
Création de tables
Formes de création de tables
Les contraintes d’intégrité
Modification de la structure d’une table
Suppression de tables
Changement du nom d’une table
Création de synonymes pour une table
©Sonia LOULOU & Sameh CHAABANE 1
Cours Bases de données
CHAPITRE 4
LE LANGAGE DE DÉFINITION DE
DONNÉES (LDD)
1. Introduction
Le LDD permet de définir des bases de données et des objets qui les composent : schémas,
tables, synonymes, clusters, vues, ….
La définition d’un objet inclut généralement sa création, sa modification et sa suppression.
Les commandes du langage de définition des données de SQL sont :
Create : création d’objets,
Alter : modification de structures d’objets,
Drop : suppression d’objets.
1. Création de tables
La création d’une table consiste à définir son nom, les colonnes qui la composent, leurs types
et éventuellement les contraintes d’intégrités nécessaires. Elle se fait à l’aide de la
commande Create table.
Le nom de la table doit être unique dans le schéma et ne doit pas être un mot clé SQL.
Oracle dispose de deux formes pour cette commande :
2.1 Formes de création de tables
1ère forme Création simple : elle permet de définir uniquement la structure de la table.
Create Table Nom_de_la_table
(Nom_de_colonne1 Type [(taille)] [default ValeurParDefaut] [NULL/NOT NULL]
[Contrainte de colonne] […],
Nom_de_colonne2 Type [(taille)] [default ValeurParDefaut ][NULL/NOT NULL]
[Contrainte de colonne],
...
Nom_de_colonneN Type [(taille)] [default ValeurParDefaut ][NULL/NOT NULL]
[Contrainte de colonne],
[Contraintes de table]) ;
La taille indique la taille maximale que peut avoir une colonne.
©Sonia LOULOU & Sameh CHAABANE 2
Cours Bases de données
L'option Not Null, placée immédiatement après le type de donnée permet de préciser au
système que la saisie de ce champ est obligatoire. Elle est utilisée implicitement pour les
colonnes clés dans une table.
Pour chaque colonne que l'on crée, il faut préciser le type de données que le champ va
contenir. Exemples de types de données possibles :
Type de donnée Syntaxe Description
Chaîne de caractères de longueur fixe n
Type alphanumérique CHAR(n)
(1<=n<=255)
Chaîne de caractères de longueur variable
Type alphanumérique VARCHAR2(n)
(1<=n<=2000)
Nombre entier ou décimal de 40 positions au
Type numérique NUMBER maximum, de la forme : chiffres de 0 à 9 avec les
signes ‘+’, ‘-‘ et le point décimal ‘.’
Nombre de n chiffres dont [optionnellement après
Type numérique NUMBER(n,[d])
la virgule]
Type horaire DATE Date dont le format standard est DD-MON-YY
… … …
Exemple Créer la table « Produit » ayant comme schéma :
Produit (NumProd, DesProd, Couleur, Poids, QteStk, QteSeuil, PrixProd)
Avec
NumProd : de type numérique de taille 6
DesProd : de type caractère variable de taille 15.
Couleur : de type caractère sur une position
Poids : de type numérique sur huit positions dont trois chiffres après la virgule.
QteStk : de type numérique sur sept positions dont trois chiffres après la virgule.
QteSeuil : de type numérique sur sept positions dont trois chiffres après la virgule.
Prix : de type numérique sur dix positions dont sept chiffres avant la virgule.
Create table Produit
(
NumProd Number(6) not null ,
DesProd Varchar2(15),
Couleur Char(1),
Poids Number(8,3),
QteStk Number(7,3),
QteSeuil Number(7,3),
PrixProd Number(10,3)
);
©Sonia LOULOU & Sameh CHAABANE 3
Cours Bases de données
2ème forme Création + Insertion : Il est possible de créer une table avec insertion directe de
lignes. Les lignes à insérer sont alors récupérées d'une ou de plusieurs tables et/ou vues
existantes. La syntaxe de cette 2ème forme est la suivante:
Create Table Nom_de_la_table [(Nom_de_colonne1, Nom_de_colonne2, ...,
Nom_de_colonneN )]
As Requête ;
Si la définition des colonnes est omise, Oracle hérite les noms de colonnes de la requête. Il
est possible de définir une table à partir de plusieurs tables sources.
La requête est une opération de sélection qui indique les colonnes sources, les tables
auxquelles elles appartiennent et éventuellement des critères de sélection.
2.2 Les contraintes d’intégrité
Une contrainte d'intégrité est une clause permettant de contraindre la modification de tables,
faite par l'intermédiaire de requêtes utilisateurs, afin que les données saisies dans la base
soient conformes aux données attendues. Ces contraintes peuvent être exprimées tout au long
du cycle de vie de la BD.
On peut utiliser des contraintes pour imposer l’une des règles suivantes :
Donner une valeur par défaut à un champ (Default),
Imposer que tout attribut ait une valeur dans la table (Not Null),
Imposer que la valeur d’un attribut donné soit unique dans la table (Unique),
Identifier une ou plusieurs colonnes comme étant une clé primaire (Primary Key),
Imposer que la valeur d’une ou de plusieurs colonnes existe dans une autre table
(Foreign Key), ….
Imposer que la valeur d’une ou de plusieurs colonnes soit conforme à une
expression donnée (Check).
a. Définir une valeur par défaut : Default
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.
La clause Default doit être suivie par la valeur à affecter. Cette valeur peut être l’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
©Sonia LOULOU & Sameh CHAABANE 4
Cours Bases de données
le mot clé Current_Date ou Sysdate(date de saisie)
le mot clé Current_Time (heure de saisie)
le mot clé Current_Timestamp (date et heure de saisie)
b. Forcer la saisie d'un champ : Not Null
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é
c. Imposer l'unicité d'une valeur : Unique
La clause Unique permet de garantir que toutes les valeurs d'une colonne d'une table seront
différentes.
Syntaxe 1
Définition de contraint Unique comme contrainte de table :
Constraint Nom_de_la_contrainte Unique(NomColonne)
Syntaxe 2
Définition de contrainte Unique comme contrainte de colonne : en ajoutant Unique
devant le nom de la colonne.
NomColonneType Unique
d. Définition de clé primaire : Primary Key
L'ensemble des colonnes faisant partie de la table en cours et permettant de désigner de façon
unique un tuple est appelé clé primaire. Elle est 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 ni prendre la valeur Null ni avoir simultanément la même combinaison de valeurs.
Syntaxe 1
Définition de clé primaire comme contrainte de table :
Constraint Nom_de_la_contrainte Primary Key(NomColonne1, NomColonne2, ...)
Syntaxe 2
Définition de la clé primaire comme contrainte de colonne : en ajoutant devant la
colonne clé primaire
NomColonne Type Primary Key,
©Sonia LOULOU & Sameh CHAABANE 5
Cours Bases de données
Remarque
Dans le cas de clé primaire multiple, la clé primaire doit être créée obligatoirement comme
contrainte de table (Syntaxe 1).
Exemple
Améliorons l’exemple de création de la table Produit en ajoutant les contraintes suivantes :
NumProd est la clé primaire de la table.
deux produits différents ne peuvent pas avoir la même désignation.
CREATE TABLE Produit CREATE TABLE Produit
( (
NumProd Number(6) primary key,
NumProd Number(6),
DesProd Varchar2(15) unique,
Couleur Char(1), DesProd Varchar2(15) ,
Poids Number(8,3), Couleur Char(1),
QteStk Number(7,3),
Poids Number(8,3),
QteSeuil Number(7,3),
Prix Number(10,3) QteStk Number(7,3),
); QteSeuil Number(7,3),
Prix Number(10,3),
Constraint UQ_DesProd Unique(DesProd),
Constraint pk_prd Primary Key (NumProd)
);
e. Définition de clé étrangère : Foreign Key, References
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.
Syntaxe 1
Définition de clé étrangère comme contrainte de table :
Constraint Nom_de_la_contrainte Foreign Key(NomColonne1, NomColonne2, ...)
References nom_table_étrangère (clé_primaire_table_étrangère)
Syntaxe 2
La clé étrangère est définie comme contrainte de colonne en ajoutant devant la colonne
clé étrangère :
References nom_table_étrangère (clé_primaire_table_étrangère)
Remarque
Il est impossible de créer une clé étrangère si la clé primaire associée n'existe pas.
©Sonia LOULOU & Sameh CHAABANE 6
Cours Bases de données
Exemple
Considérons le schéma suivant :
Magasin (NumMag, Adresse, Surface)
Produit (NumProd, DesProd, Couleur, Poids, QteStk, QteSeuil, Prix,
#CodMag)
La commande de création de la table Magasin étant :
Create Table Magasin
(
NumMag Number(6) primary key,
Adresse Varchar2(30),
Surface Number(7,3)
);
La commande de création de la table Produit peut être écrite de deux façons:
Solution 1 : Clé étrangère comme contrainte de table
Create Table Produit
(
NumProd Number(6) Primary key ,
DesProd Archar2(15),
Couleur Char(1),
Poids Number(8,3),
QteStk Number(7,3),
QteSeuil Number(7,3),
Prix Number(10,3),
CodMag Number(6),
Constraint Fk_Prd Foreign key (CodMag) references Magasin(NumMag)
);
Solution 2 : Clé étrangère comme contrainte de colonne
Create Table Produit
(
NumProd Number(6) Primary key ,
DesProd Varchar2(15),
Couleur Char,
Poids Number(8,3),
QteStk Number(7,3),
QteSeuil Number(7,3),
Prix Number(10,3),
CodMag Number(6) References Magasin (NumMag)
);
©Sonia LOULOU & Sameh CHAABANE 7
Cours Bases de données
©Sonia LOULOU & Sameh CHAABANE 8
Cours Bases de données
f. Emettre une condition sur un champ : Check
Il est possible de définir une condition sur un champ grâce à la clause Check() comportant une
expression logique. Si la valeur saisie est différente de Null, le SGBD va effectuer un test
grâce à la condition logique.
Syntaxe 1
Définition de contrainte Check comme contrainte de table :
Constraint Nom_de_la_contrainte Check (NomColonne Condition)
Syntaxe 2
Définition de contrainte Check comme contrainte de colonne en ajoutant devant la
définition de la colonne :
Check (NomColonne Condition)
La condition sur la colonne peut utiliser :
un opérateur de comparaison
la clause between val1 and val2
la clause in (liste de valeurs)
Exemple
Améliorons l’exemple de création de la table Produit en ajoutant les contraintes suivantes :
- le poids d'un produit doit être positif.
- pour un produit, la quantité en stock doit être supérieure ou égale à la quantité
seuil.
- la quantité d'un produit doit être comprise entre 0 et 1000.
- la couleur d'un produit ne peut être que 'R', 'N', 'G' ou 'B'
Create Table Produit
(
NumProd Number(6) Primary key ,
DesProd Varchar2(15),
Couleur Char(1),
Poids Number(8,3) Check (Poids > 0),
QteStk Number(7,3),
QteSeuil Number(7,3),
Prix Number(10,3),
CodMag Number(6) References Magasin(NumMag),
Constraint CK2_Produit Check (QteStk >= QteSeuil ),
Constraint CK3_Produit Check (QteStk Between 0 And 1000),
Constraint CK4_Produit Check (Couleur IN ('R', 'N','G','B'))
);
©Sonia LOULOU & Sameh CHAABANE 9
Cours Bases de données
Remarque
Le mot clé Constraint est utilisé pour attribuer un nom à la contrainte. 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.
2. Modification de la structure d’une table
Les tables gérées par oracle peuvent avoir une structure dynamique (ajout de nouvelles
colonnes, suppression de colonnes, renommer une colonne, ajout de contraintes et/ou
modification des types de colonnes déjà existantes).
Ces possibilités de modification de tables se traduisent par trois variantes de la commande
Alter Table.
1ère variante ajout de nouvelles colonnes et/ou des contraintes nécessaires à une table
Alter Table Nom_de_la_table
ADD (NomColonne1 Type [(taille)] [NULL | NOT NULL] …,
...
NomColonneN Type [(taille)] [NULL | NOT NULL] …[,
Constraint Nom_de_la_contrainte ………………………]);
Exemple 1
Ajouter les champs « Gérant » de type caractère variable de taille 20 et « Ville » de type
caractère variable de taille 15 contenant par défaut Sfax à la table magasin.
Alter Table Magasin
ADD (Gérant Varchar2(20), Ville Varchar2(15) Default 'Sfax');
Exemple 2
On suppose que la table Produit a été créée sans clé primaire ni clé étrangère.
Ajouter la contrainte clé primaire pour le champ NumProd
Alter Table Produit
ADD (Constraint PK_Produit Primary key (NumProd));
Ajouter la contrainte clé étrangère pour le champ CodMag
Alter Table Produit
ADD(Constraint FK_Produit Foreign Key (CodMag) References
Magasin(NumMag));
©Sonia LOULOU & Sameh CHAABANE 10
Cours Bases de données
Ajouter les contraintes de vérification pour que le champ QteSeuil soit > 0 et le
champ DesProd soit unique.
Alter Table Produit
ADD (Constraint CK5_Produit Check (QteSeuil > 0),
Constraint UC_Produit Unique (DesProd));
2ème variante Modification de la définition de colonnes existantes.
Les modifications possibles sont : accroître la taille, réduire la taille (seulement si la colonne
ne contient que des valeurs nulles), modifier le type (seulement si la colonne ne contient que
des valeurs nulles) et supprimer l’interdiction de présence de valeurs nulles.
Alter Table Nom_de_la_table
MODIFY (Nom_de_colonne1 Type [(taille)] [NULL | NOT NULL\...],
...
Nom_de_colonneN Type [(taille)] [NULL | NOT NULL\...]);
Exemple1
Modifier le champ Gérant de la table Magasin de manière qu'il devienne caractère variable
de taille 10.
Alter Table Magasin
MODIFY (Gérant varchar2(10));
Exemple2
Modifier la table Produit de manière à ce que la valeur par défaut de QteSeuil soit égale à
10.
Alter Table Produit
MODIFY (QteSeuil Default 10);
3ème variante Suppression de colonnes ou de contraintes existantes.
La suppression de colonnes existantes est possible en utilisant la commande :
ALTER TABLE Nom_de_la_table
DROP (NomColonne1, …, NomColonneN) ;
Exemple
Supprimer les colonnes Gérant et Ville de Magasin
Alter Table Magasin
DROP (Gérant, Ville);
4ème variante Renommer une colonne existante
Le changement du nom d’une colonne existante est possible en utilisant la
commande :
©Sonia LOULOU & Sameh CHAABANE 11
Cours Bases de données
ALTER TABLE Nom_de_la_table
Rename column AncienNomColonne to NouveauNomColonne ;
La suppression d’une contrainte de clé primaire est possible en utilisant la commande :
ALTER TABLE Nom_de_la_table
DROP Primary Key [Cascade] ;
Remarque
L'option Cascade est ajoutée pour pouvoir supprimer une clé primaire référencée.
Exemple
Supprimer dans l'ordre les clés primaires des tables Magasin et Produit.
Alter Table Magasin
DROP Primary key Cascade;
Alter Table Produit
DROP Primary Key;
La suppression d’une contrainte autre que la clé primaire :
Alter Table Nom_de_la_table
DROP Constraint Nom_de_la_contrainte ;
Où Le nom de la contrainte est celui de la contrainte à supprimer.
Remarque
Pour retrouver les noms des différentes contraintes avec leurs types, on peut utiliser la
commande suivante :
Select Constraint_Name, Constraint_Type
From User_Constraints
[Where Table_name = 'NOMTABLE'];
Et pour une liste complète d’informations sur les contraintes, on peut taper :
Select*
From User_Constraints
[Where Table_name = 'NOMTABLE'];
Il est à noter pour cette commande, le nom de la table doit être écrit en majuscule.
Exemple 1 Supprimer la contrainte clé étrangère de la table Produit.
Alter Table Produit
DROP Constraint FK_Prd;
Exemple 2 Modifier la table Produit de manière que la couleur soit quelconque.
©Sonia LOULOU & Sameh CHAABANE 12
Cours Bases de données
Alter Table Produit
DROP Constraint CK4_Produit;
3. Suppression de tables
Certaines tables peuvent devenir inutiles dans certains cas et doivent être supprimées.
La suppression se fait par la commande :
Drop Table Nom_de_la_table [Cascade Constraints] ;
Le mot clé Cascade Constraints permet de supprimer toutes les contraintes d’intégrité
référentielles qui se reflètent à la clé primaire de la table à supprimer.
La suppression d’une table entraîne la suppression, dans le dictionnaire, de sa définition ainsi
que les synonymes et index correspondants. Dans ce cas il faut redéfinir toutes les vues créées
sur la table supprimée.
Exemple Supprimer dans l'ordre les tables Magasin et Produit.
Drop Table Magasin Cascade Constraints ;
Drop Table Produit;
4. Changement du nom d’une table
On peut renommer une table en utilisant la commande Rename dont la syntaxe est :
Rename Ancien_nom To Nouveau_nom ;
Exemple Modifier le nom de Produit en Article.
Rename Produit To Article ;
5. Création de synonymes pour une table
Il s'agit de faire une copie d’une table avec un nom différent. La création de synonyme se fait
avec la commande :
Create Synonym Nom_synonyme For Nom_table ;
Exemple Créer un synonyme M pour la table Magasin
Create Synonym M For Magasin;
Remarque
La suppression d’un synonyme se fait avec la commande :
Drop Synonym Nom_synonym ;
Exemple Supprimer le synonyme M.
Drop Synonym M;
©Sonia LOULOU & Sameh CHAABANE 13
