Création et modification de la structure d’une BD
I) Introduction :
Consiste à créer et modifier les différentes structures de la BD à l’aide du langage SQL (Structured Query
Language). Ce langage est composé de trois familles de Commandes :
• LDD : Langage de définition des données
• LMD : Langage de manipulation des données
• LCD : Langage de contrôle des données
Nous nous intéressons uniquement de LDD et LMD
II) Création d’une base de données en mode commande :
Soit la représentation textuelle relative à la base de données « gestion_commande » suivante :
Article (Code_art, des_art, PU, qte_stock)
Client (Code_cl, nom_cl, Pren_cl, adr_cl, tel_cl, dn, age)
Commande (Num_com, date_com, code_client#)
Detail_commande (num_com#, code_art#, qte_com)
Description des colonnes
Colonnes Description
Code_cl Entier auto incrémenté
nom_cl, pren_cl Chaine obligatoire de 15 caractères au maximum
adr_cl Chaine de 60 caractères au maximum
Tel_cl Entier de 8 chiffres
dn Date
age Entier valeur comprise entre 5 et 99
Code_art Chaine de 5 caractères au maximum
Des_art Chaine de 50 caractères au maximum elle doit être unique
Pu Réel positif de 8 chiffres dont 3 chiffres après la virgule
Qte_stk Entier positif de 5 chiffres valeur par défaut 0
Num_com Entier auto incrémenté
date_com Date valeur par défaut date d’aujourd’hui
Qte_com Entier
a) Création d’une base de données :
CREATE DATABASE nom_base ;
Q1 : Écrire la commande SQL permettant de créer la base de données "gestion_commandes"
CREATE DATABASE gestion_commandes;
b) Création d’une table :
CREATE TABLE nom_table
( nom_colonne1 Type [[NOT] NULL] [DEFAULT valeur] [contrainte_colonne1] ,
nom_colonne2 Type [[NOT] NULL] [DEFAULT valeur] [contrainte_colonne2] ,
…
);
NB :
Le nom de la table doit être unique au niveau de la BD et ne doit pas être un mot clé (réservé).
Les mots entre crochet [ ] sont facultatifs.
NULL : indique que la colonne n’est pas obligatoire.
NOT NULL : indique que la colonne est obligatoire.
DEFAULT valeur : cette option ne doit pas être indiquée lorsque la colonne est obligatoire (NOT NULL).
b.1) Les types de données :
INT (n) : numérique à n chiffres.
DECIMAL (n, m) : réel à n chiffres (virgule comprise) avec m chiffres après la virgule.
VARCHAR (n) : chaîne de caractères de longueur variable dont la taille maximale est n (chaîne limitée à 255c).
Date : date
TIME : temps
CHAR(n) : chaîne de caractères de longueur n fixe (chaîne limitée à 255c).
TEXT : chaine de caractères de taille qui dépasse 255c. pas de valeur par défaut pour le type TEXT
4ème Science de l’informatique (L.S. Avenue de l’indépendance) Mr Walim ZOUARI
b.2) Les contraintes d’intégrité :
Contrainte de colonne :
L’option [contrainte_colonne] permet de contrôler la validité et la cohérence des valeurs d’une colonne.
Syntaxe générale :
[CONSTRAINT nom_contrainte]
{PRIMARY KEY
|REFERENCES nom_table [(nom_colonne)] [ON DELETE CASCADE] [ON UPDATE CASCADE]
|CHECK (condition)}
Le mot CONSTRAINT est optionnel et sert à attribuer un nom à la contrainte désignée par le mot
nom_contrainte.
PRIMARY KEY : spécifie que la colonne est utilisée comme clé primaire.
REFERENCES définit une contrainte d’intégrité référentielle elle indique que la colonne est une clé étrangère,
nom_table est la table mère et nom_colonne est la clé primaire de la table mère et il est donné lorsque le nom de la
clé primaire de la table mère est différent du nom de la clé étrangère.
ON DELETE CASCADE : est une option qui permet de maintenir la contrainte d’intégrité référentielle
en supprimant automatiquement dans la table FILLE les enregistrements ayant des valeurs de clé
étrangère dépendants d’une valeur d’une clé primaire si cette dernière est supprimée dans la table MERE.
Exemple :
Si on supprime le client ayant pour code « 2 » à partir de la table mère « Client », tous les
enregistrements dans la table fille Commande liés à cette clé primaire autrement dit ; ayant « 2 »
comme clé étrangère seront automatiquement supprimés.
ON UPDATE CASCADE : est une option qui permet de maintenir la contrainte d’intégrité référentielle
en modifiant automatiquement dans la table FILLE les valeurs des champs d’une clé étrangère dépendant
d’une valeur d’une clé primaire si cette dernière est modifiée dans la table MERE.
Exemple :
Si on Modifie la clé primaire du client ayant pour code « 2 » par « 202 » dans la table mère « Client »,
toutes les clés étrangères appartenant aux enregistrements (dans la table fille Commande) liés à cette
clé primaire seront automatiquement modifiés par la nouvelle valeur de clé primaire « 202 ».
CHECK est un mot clé associé à une condition qui doit être vérifiée pour chaque valeur insérée.
Exemple :
.........Check ( Prix > 0 ) ;
.........Check ( Qte_stock >=0 ) ;
.........Check ( sexenf =’G’ OR sexenf=’F’) ;
.........Check (NoteC BETWEEN 0 AND 20 ) ;
.........Check ( SituationFam IN ( ‘C’, ‘En couple’ , ‘M’, ‘D’, ‘V’, ) ) ;
.........Check ( NbrEnf IN ( 0 , 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9, 10 ) ) ;
Q2 : Créer les tables « CLIENT », et « ARTICLE » de cette base
CREATE TABLE Client CREATE TABLE Article
( Code_cl INT auto_increment PRIMARY KEY, ( Code_art VARCHAR(5) PRIMARY KEY,
Nom_cl VARCHAR(15) NOT NULL, Des_art VARCHAR(50) UNIQUE,
Pren_cl VARCHAR(15) NOT NULL, PU DECIMAL(8,3) CHECK (PU>0),
Adr_cl VARCHAR(60), Qte_stock INT(5) DEFAULT 0 CHECK (PU>=0)
Tel_cl INT(8), ) ;
Dn Date,
Age INT(2) CHECK (age between 5 and 99)
) ;
4ème Science de l’informatique (L.S. Avenue de l’indépendance) Mr Walim ZOUARI
Contrainte de table :
C’est une contrainte appliquée à plusieurs colonnes.
Syntaxe générale :
[constraint nom_contrainte]
{PRIMARY KEY} (colonne1, colonne2,…)
|foreign KEY (colonne1, colonne2,…) REFERENCES nom_table [(colonne1, colonne2,…)]
|[ON DELETE CASCADE] [ON UPDATE CASCADE]
|CHECK (condition)}
PRIMARY KEY : spécifie que la colonne1, colonne2 sont utilisées comme clé primaire.
FOREIGN KEY : définit une contrainte d’intégrité référentielle relative à une ou plusieurs colonnes. Le nom de
la table précisé après le mot REFERENCES est celui de la table mère. Les noms des colonnes sont ceux des colonnes
vers lesquelles on se réfère elles doivent être encadrée par les parenthèses.
Q3 : Créer les tables « COMMANDE », et « DETAIL_COMMANDE » de cette base
CREATE TABLE Commande CREATE TABLE Detail_commande
( Num_com INT auto_increment PRIMARY KEY, (Num_com INT NOT NULL,
Date_com DATE DEFAULT NOW(), Code_art VARCHAR(5) NOT NULL ,
Code_client INT, Qte_com INT,
CONSTRAINT fk_cl FOREIGN KEY (Code_client) CONSTRAINT pk_com PRIMARY KEY (Num_com,
REFERENCES Client (Code_cl) Code_art ),
) ; CONSTRAINT fk1_com FOREIGN KEY (Num_com)
REFERENCES Commande(Num_com),
CONSTRAINT fk2_com FOREIGN KEY (Code_art)
REFERENCES Article(Code_art)
) ;
REMARQUE1
Si la table a une clé primaire composée de plusieurs colonnes, alors il est impossible d'indiquer PRIMARY
KEY au niveau des colonnes.
REMARQUE2
Soit la représentation textuelle suivante relative à la gestion des réservations des chambres dans un hôtel
CHAMBRES (NumCh, NumHot#, Nbr_lit)
RESERVATIONS (NumCin# , NumCh#, NumHot# , Datedeb, Datefin, Nbr_pers)
CLIENTS (NumCin, NomCli, PrenCli, ProfCli, AdrCli, TelCli)
il arrive qu'une clé
primaire, composée de
plusieurs colonnes, soit
elle-même clé étrangère
d'une autre table. On
indique alors cette clé
étrangère composée
après la définition des
colonnes, grâce au mot
clé FOREIGN KEY suivi
des colonnes clés étrangères, puis de REFERENCES, le nom de la table référencée ainsi que le nom des
colonnes référencées entre parenthèses.
4ème Science de l’informatique (L.S. Avenue de l’indépendance) Mr Walim ZOUARI
III) Modification de la structure d’une BD :
Il est possible de modifier la structure d’une table à tout moment et ceci en tapant les commandes SQL
éventuelles suivantes :
ALTER TABLE nom_table
[ADD COLUMN def_colonne]
[ADD CONSTRAINT def_contrainte]
[MODIFY|ALTER def_colonne]
[CHANGE nom_colonne1 nom_colonne2 type_colonne]
[RENAME nom_colonne1 TO nom_colonne2]
[DROP COLUMN nom_colonne]
[DROP CONSTRAINT nom_contrainte]
[ENABLE|DISABLE nom_contrainte] ;
COMMANDE RÔLE EXEMPLES
ALTER TABLE nom_table Ajouter des nouvelles Ajouter une colonne Email (80 caractères) obligatoire à
ADD [COLUMN] definition_colonne ; colonnes. la table « client ».
ALTER TABLE CLIENT
ADD EMAIL VARCHAR (80) NOT NULL;
ALTER TABLE nom_table Modifier certaines Augmenter la taille de la colonne Adr_cl de 20
MODIFY [COLUMN] nouv_def_colonne ; caractéristiques d’une caractères.
colonne existante. ALTER TABLE CLIENT
ALTER TABLE nom_table MODIFY ADR_CL VARCHAR (80) ;
ALTER [COLUMN] nouv_def_colonne ; Rendre la colonne des_art de la table article obligatoire
ALTER TABLE ARTICLE
MODIFY DES_ART VARCHAR (50) NOT
NULL ;
ALTER TABLE nom_table Modifie le nom d’une Renommer la colonne code_client de la table commande
RENAME [COLUMN] Ancien_nom_colonne colonne. par code_cl
TO nouv_nom_colonne; ALTER TABLE COMMANDE
Rename n’est pas fonctionnelle avec Mysql CHANGE CODE_CLIENT CODE_CL INT ;
ALTER TABLE nom_table
CHANGE [COLUMN] Ancien_nom_colonne
nouv_nom_colonne type_colonne;
ALTER TABLE nom_table Supprimer une Supprimer la colonne age de la table client;
DROP [COLUMN] nom_colonne ; colonne existante. ALTER TABLE CLIENT
DROP age ;
ALTER TABLE nom_table Ajouter une nouvelle Ajouter la contrainte nommée « ct1 » qui permet de
ADD CONSTRAINT [nom_contrainte] contrainte à une table. vérifier que la Qte_com est >0.
Définition_contrainte ; ALTER TABLE DETAIL_COMMANDE
ADD CONSTRAINT CT1 CHECK (Qte_com >0);
ALTER TABLE nom_table Désactiver une Désactiver la contrainte d’intégrité de domaine « ct1 ».
DISABLE CONSTRAINT nom_contrainte ; contrainte d’intégrité. Si
la contrainte d’intégrité ALTER TABLE DETAIL_COMMANDE
concerne la clé primaire,
il faut supprimer les clés DISABLE CONSTRAINT CT1 ;
étrangères dans les tables
qui s’y réfèrent avant de
procéder à la
désactivation de la clé
primaire.
ALTER TABLE nom_table Activer une contrainte Activer la contrainte d’intégrité de domaine « ct1 ».
ENABLE CONSTRAINT nom_contrainte ; d’intégrité. ALTER TABLE DETAIL_COMMANDE
ENABLE CONSTRAINT CT1 ;
ALTER TABLE nom_table Supprimer une Supprimer la contrainte d’intégrité de domaine « ct1 ».
DROP CONSTRAINT nom_contrainte ; contrainte d’intégrité ALTER TABLE DETAIL_COMMANDE
d’une table.
DROP CONSTRAINT CT1;
ENABLE et DISABLE ne sont pas fonctionnelles avec Mysql
4ème Science de l’informatique (L.S. Avenue de l’indépendance) Mr Walim ZOUARI
d) Suppression d’une table :
DROP TABLE nom_table ;
Exemple : DROP TABLE client ;
e) Suppression d’une base de données :
DROP DATABASE nom_base ;
Exemple : DROP DATABASE gestion_commandes;
4ème Science de l’informatique (L.S. Avenue de l’indépendance) Mr Walim ZOUARI
