SQL : mise-à-jour de BD
• Commandes de définition de données (DDL)
• Commandes de mise-à-jour de données (DML)
• Contraintes d’intégrité
• Triggers
Cours Bases de données (Licence) 6-1
SQL : mises-à-jour
SQL permet d’interroger (langage de requêtes) et de
créer, mettre à jour et supprimer des bases de
données.
• SQL-DDL : création, modification et suppression de
schémas
création, modification et suppression de schémas de
relation (tables vides)
définition de clés et d’autres contraintes
• SQL-DML : création, modification et suppression de
données
insertion, modification et suppression de n-uplets
Cours Bases de données (Licence) 6-2
Commandes DDL
Création de schémas :
CREATE SCHEMA nom_schema AUTHORIZATION nom_utilisateur
Création de tables :
Spécifie un nouveau schéma de relation
Forme :
CREATE TABLE nom_table
(Attribute_1 <Type>[DEFAULT <value>],
Attribute_2 <Type>[DEFAULT <value>],
…
Attribute_n <Type>[DEFAULT <value>]
[<Constraints>])
Cours Bases de données (Licence) 6-3
Exemple
Définition de la relation Project
CREATE TABLE Project
(Pno CHAR(3),
Pname VARCHAR(20),
Budget DECIMAL(10,2)DEFAULT 0.00
City CHAR(9));
Cours Bases de données (Licence) 6-4
Types de données SQL
Numériques :
INT, SMALLINT. Possibilité de DEFAULT AUTOINCREMENT
REAL (FLOAT), DOUBLE PRECISION
DECIMAL(i,j) : i=précision (nombres de chiffres); j=échelle
• DECIMAL(4,3) = [0.000 … 9.999]
Chaînes de caractères :
CHAR(n), VARCHAR(n) (longueur variable)
Chaînes de bits:
BIT(n), BIT VARYING(n)
Dates :
DATE : YYYY-MM-DD
Heure :
TIME : HH:MM:SS
Instants de temps (timestamp) :
les champs DATE et TIME avec une précision de 10-6 secondes
Cours Bases de données (Licence) 6-5
Types utilisateurs : domaines
Pour créer un DOMAINE :
CREATE DOMAIN nom_domaine AS type
Exemple :
CREATE DOMAIN Gender AS CHAR(1)
• Utile pour pouvoir contrôler l’évolution de définitions de
types.
Oracle: CREATE DOMAIN n’est pas implanté.
Cours Bases de données (Licence) 6-6
Autres commandes DDL
DROP SCHEMA nom_schema [,…] [CASCADE | RESTRICT]
supprime les schémas indiqués
CASCADE : toutes les tables du schéma
RESTRICT : seulement les tables vides (par défaut)
DROP TABLE nom_table [,…] [CASCADE | RESTRICT]
RESTRICT : supprime la table seulement si elle n’est référencée
par aucune contrainte (clé étrangère) ou vue
CASCADE : supprime aussi toutes les tables qui « dépendent » de
nom_table
ALTER TABLE nom_table OPERATION
modifie la définition de la table
opérations:
• Ajouter (ADD), effacer (DROP), changer (MODIFY) attributs et
contraintes
• changer propriétaire, …
Cours Bases de données (Licence) 6-7
Insertion de tuples
INSERT INTO table [ ( column [, ...] ) ]
{VALUES ( { expression | DEFAULT } [, ...] ) | query }
• en spécifiant des valeurs différentes pour tous les attributs
dans l’ordre utilisé dans CREATE TABLE :
INSERT INTO R
VALUES (value(A1), …, value(An))
• en spécifiant les noms d’attributs (indépendant de l’ordre) :
INSERT INTO R(Ai, …, Ak)
VALUES (value(Ai), …, value(Ak))
• insertion du résultat d’une requête (copie) :
INSERT INTO R (requête_SQL)
Cours Bases de données (Licence) 6-8
Exemples d’insertion
Emp (Eno, Ename, Title, City) Project(Pno, Pname, Budget, City)
Pay(Title, Salary) Works(Eno, Pno, Resp, Dur)
Insertion d’un nouvel employé :
INSERT INTO Emp(Eno, Ename, Title, City)
VALUES (‘E24’,‘Martin’,‘Programmeur’,‘Paris’)
Insertion dans Pay des n-uplets dont les titres existent dans Emp; avec le
salaire à 0 :
INSERT INTO Pay
(SELECT DISTINCT Title, 0
FROM Emp)
Cours Bases de données (Licence) 6-9
Suppression de tuples
DELETE FROM table [ WHERE condition ]
Supprimer tous les employés qui ont travaillé dans le projet P3
pendant moins de 3 mois :
DELETE FROM Emp
WHERE Eno IN ( SELECT Eno
FROM Works
WHERE PNO=‘P3’
AND Dur < 3)
Attention : il faut aussi effacer les n-uplets correspondants dans
la table Works (cohérence des données).
Cours Bases de données (Licence) 6-10
Modification de tuples
UPDATE table
SET column = { expression | DEFAULT } [, ...]
[ WHERE condition ]
UPDATER
SET Ai=value, …, Ak=value
WHERE P
Cours Bases de données (Licence) 6-11
Exemples de modification
Emp (Eno, Ename, Title, City) Project(Pno, Pname, Budget, City)
Pay(Title, Salary) Works(Eno, Pno, Resp, Dur)
Augmenter de 5% les budgets des projets situés à Nantes qui
emploient au moins 3 personnes :
UPDATE Project
SET Budget = Budget*1.05
WHERE City = ‘Nantes’
AND Pno IN ( SELECT Pno
FROM Works
GROUP BY Pno
HAVING COUNT(*) >= 3)
Cours Bases de données (Licence) 6-12
Terminaison des requêtes/mises-
à-jours
COMMIT
pour rendre permanents les résultats des mises-à-jours
dans un programme (C, java…): EXEC SQL COMMIT;
ROLLBACK
pour annuler les résultats des mises-à-jour
dans un programme: EXEC SQL ROLLBACK;
• La sémantique de ces commandes est supportée
par les transactions (on verra plus tard).
Cours Bases de données (Licence) 6-13
Mise-à-jour en Embedded SQL
Exemple: transfert d’un montant entre deux budgets de projets
#include <stdio.h>
EXEC SQL INCLUDE SQLCA;
main() {
EXEC SQL WHENEVER SQLERROR GOTO error:
EXEC SQL CONNECT TO Company;
EXEC SQL BEGIN DECLARE SECTION;
int pno1, pno2; /* 2 numéros de projet */
int amount; /* montant du transfert */
EXEC SQL END DECLARE SECTION;
/* Code (omis) pour lire pno1, pno2 et amount */
EXEC SQL UPDATE Project
SET Budget = Budget + :amount
WHERE Pno = :pno2;
EXEC SQL UPDATE Project
SET Budget = Budget - :amount
WHERE Pno = :pno1;
EXEC SQL COMMIT RELEASE;
return(0);
error:
printf(“update failed, sqlcode = %ld\n”, SQLCODE);
EXEC SQL ROLLBACK RELEASE;
return(-1);
}
Cours Bases de données (Licence) 6-14
Contraintes d’intégrité
Cours Bases de données (Licence) 6-15
Contraintes d’intégrité
Une contrainte d’intégrité est une condition
(logique) qui doit « toujours » être satisfaites par
les données stockées dans la BD.
But : maintenir la cohérence/l’intégrité de la BD :
Vérifier/valider automatiquement (en dehors de
l’application) les données lors des mises-à-jour
(insertion, modification, effacement)
Déclencher automatiquement des mises-à-jour entre
tables pour maintenir la cohérence globale.
Cours Bases de données (Licence) 6-16
Contraintes d’attributs
PRIMARY KEY
désigne un ensemble d’attributs comme la clé primaire
de la table
FOREIGN KEY
désigne un ensemble d’attributs comme la clé étrangère
dans une contrainte référentielle
NOT NULL
spécifie qu’un attribut ne peut avoir de valeurs nulles
UNIQUE
spécifie un ensemble d’attribut dont les valeurs doivent
être distinctes pour chaque couple de n-uplets.
Cours Bases de données (Licence) 6-17
Exemple
Project(Pno, Pname, Budget, City)
Créer la table Project:
CREATE TABLE Project
(Pno CHAR(3),
Pname VARCHAR(20)UNIQUE NOT NULL,
BudgetDECIMAL(10,2)DEFAULT 0.00,
City CHAR(9),
PRIMARY KEY (Pno));
Remarque: Les attributs de la clé primaire sont toujours
UNIQUE et NOT NULL.
Cours Bases de données (Licence) 6-18
Manipulation de NULL
Les valeurs d’attributs peuvent être inconnues : NULL
• une opération avec un attribut de valeur NULL retourne NULL
• une comparaison avec un attribut de valeur NULL retourne
UNKNOWN
• UNKNOWN introduit une logique à trois valeurs :
Vrai = 1, UNKNOWN = 0.5, Faux = 0
x AND y = min(x,y), x OR y = max(x,y), not(x) = 1-x
Attention : NULL n’est pas une constante :
• « NAME = NULL » ou « NULL + 30 » sont incorrects.
Pour vérifier si la valeur d’un attribut est inconnue, on utilise IS
NULL : NAME IS NULL est correct!
Cours Bases de données (Licence) 6-19
Requête avec NULL
Project Pno Pname Budget City
1 Développement NULL Paris
2 Conception 2000 Lyon
SELECT Pno FROM Project
Réponses vide
WHERE Budget = 1000
SELECT P1.Pno FROM Project P1, Project P2
WHERE P1.Budget = P2.Budget AND P1.Pno <> P2.Pno
Cours Bases de données (Licence) 6-20
Contraintes référentielles
• Clé étrangère : Un ensemble d’attributs X d’une relation
R est une clé étrangère (foreign key) si X correspond à la
clé primaire d’une autre relation R’.
• Contrainte d’intégrité référentielle : Pour chaque n-uplet t
de la relation R avec la clé étrangère X, il existe un n-uplet
t’ dans la relation référencée R’ où t.X = t’.X (X est la clé
primaire de R’).
• Exemple : Title est la clé primaire de la table Pay
référencé par la table Emp :
Emp (Eno, Ename, Title, City) Pay(Title, Salary)
Pour chaque employé e dans la table Emp, il existe un n-uplet p dans
la table Pay avec le salaire de l’employé.
Cours Bases de données (Licence) 6-21
Maintenance automatique de
l’intégrité référentielle
La suppression (ON DELETE) ou la mise-à-jour (ON
UPDATE) d’un n-uplet référencé (de clé primaire) nécessite
une action sur le n-uplet avec la clé étrangère :
• RESTRICT : l’opération est rejetée (par défaut)
• CASCADE : supprime ou modifie tous les n-uplets avec
la clé étrangère si le n-uplet référencé est supprimée ou sa
clé est modifiée
• SET [NULL | DEFAULT] : mettre à NULL ou à la valeur
par défaut quand le n-uplet référencé est effacée/sa clé est
modifiée.
Cours Bases de données (Licence) 6-22
Exemple
Emp (Eno, Ename, Title, City) Project(Pno, Pname, Budget, City)
Pay(Title, Salary) Works(Eno, Pno, Resp, Dur)
Définition des références inter-tables
CREATE TABLE Works
( Eno CHAR(3),
Pno CHAR(3),
Resp CHAR(15),
Dur INT,
PRIMARY KEY (Eno,Pno),
FOREIGN KEY (Eno) REFERENCES Emp(Eno)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (Pno) REFERENCES Project(Pno));
Cours Bases de données (Licence) 6-23
Contraintes d’attribut et de domaine
CHECK <contrainte>
• Spécifie une condition que chaque n-uplet doit satisfaire:
CREATE TABLE Project
(Pno CHAR(3),
Pname VARCHAR(20),
Budget DECIMAL(10,2)DEFAULT 0.00
CHECK (BUDGET >= 0),
City CHAR(9))
PRIMARY KEY (Pno));
• Peut être utilisé pour définir des contraintes de domaine :
CREATE DOMAIN Gender AS CHAR(1)
CHECK (VALUE IN (‘F’, ‘M’));
Cours Bases de données (Licence) 6-24
Contraintes de n-uplets
La condition est vérifiée chaque fois qu’un n-uplet est inséré
ou modifié; la mise-à jour (transaction) est rejetée si la
condition est fausse.
Exemple :
CREATE TABLE Works
(Eno CHAR(3), Pno CHAR(3),
Resp CHAR(15), Dur INT,
PRIMARY KEY (Eno,Pno),
FOREIGN KEY (Eno) REFERENCES Emp(Eno)
ON DELETE SET NULL
ON UPDATE CASCADE,
FOREIGN KEY (Pno) REFERENCES Project(Pno),
CHECK (NOT(PNO<‘P5’) OR Dur>18));
Cours Bases de données (Licence) 6-25
Contraintes de n-uplets complexes
Un projet ne peut avoir plus de 2 employés avec une
affectation de plus de 48 mois :
CREATE TABLE Works
(Eno CHAR(3),
Pno CHAR(3),
Resp CHAR(15),
Dur INT,
PRIMARY KEY (Eno,Pno),
FOREIGN KEY (Eno) REFERENCES Emp(Eno)
ON DELETE SET NULL
ON UPDATE CASCADE;
FOREIGN KEY (Pno) REFERENCES Project(Pno),
CHECK(3 > ALL
(SELECT COUNT(Eno) FROM Works
WHERE Dur > 48 GROUP BY Pno));
Cours Bases de données (Licence) 6-26
Contraintes de tables : Assertions
Contraintes globales sur plusieurs relations.
Forme :
CREATE ASSERTION name CHECK (condition)
Exemple: le salaire total des employés du projet P5 ne peut
dépasser 500
CREATE ASSERTION salary-control CHECK
(500 >= (SELECT SUM(Salary)
FROM Emp E, Pay P, Works W
WHERE W.Pno = ‘P5’
AND W.Eno = E.Eno
AND E.Title = P.Title));
Cours Bases de données (Licence) 6-27
Analyse des contraintes
Les contraintes sont-elles cohérentes entre elles ?
• problème de preuve en logique
Quand vérifier une contrainte :
• Avant ou après la mise-à-jour ?
selon le type de mise-à-jour et de contrainte
• RESTRICT (avant)
• Insertion (clé: avant), update (après)
• À chaque opération ou à la validation de la transaction ?
selon la « granularité » des données impliquées :
• à chaque opération : contraintes d'attribut, de domaine et n-uplets
• à la validation de la transaction : contraintes référentielles, assertions
Cours Bases de données (Licence) 6-28
Triggers
« Règles actives » généralisant les contraintes d’intégrité
Définition ECA :
• Evénement (E) :
une mise-à-jour de la BD qui active le trigger
• Condition (C):
un test ou une requête devant être vérifié lorsque le trigger
est activé (une requêtes est vraie si sa réponse n’est pas vide)
• Action (A):
une procédure exécutée lorsque le trigger est activé et la
condition est vraie : E,C → A
Cours Bases de données (Licence) 6-29
Exécution des triggers
Moment de déclenchement de l’action A par rapport
à l’évènement E (maj. activante) :
A est exécuté
avant (before) ou après (after) E
à la place de (instead of) de E (possible que sur les
vues)
Nombre d’exécutions par déclenchement :
une exécution de l’action A par n-uplet modifié
une exécution de l’action A par événement (maj.
activante)
Cours Bases de données (Licence) 6-30
Syntaxe (Oracle)
{ CREATE | REPLACE } TRIGGER <nom>
// Evénement
{BEFORE | AFTER | INSTEAD OF}
{INSERT | DELETE | UPDATE [OF <attribut, …>]}
ON <table>
[REFERENCING [NEW AS <nouv>] [OLD AS <anc>]]
[FOR EACH ROW]
// Condition
[WHEN (<condition SQL>) THEN]
// Action
<Procédure SQL>
Cours Bases de données (Licence) 6-31
Contrôle d’intégrité
Emp (Eno, Ename, Title, City)
Vérification de la contrainte de clé à l’insertion d’un
nouvel employé :
CREATE TRIGGER InsertEmp
BEFORE INSERT ON Emp
REFERENCING NEW AS N
FOR EACH ROW
WHEN EXISTS
(SELECT * FROM Emp WHERE Eno=N.Eno)
THEN
ABORT;
Cours Bases de données (Licence) 6-32
Contrôle d’intégrité
Emp (Eno, Ename, Title, City) Pay(Title, Salary)
Suppression d’un titre et des employés
correspondants (ON DELETE CASCADE) :
CREATE TRIGGER DeleteTitle
AFTER DELETE ON Pay
REFERENCING OLD AS O
FOR EACH ROW
BEGIN
DELETE FROM Emp WHERE Title=O.Title
END;
Cours Bases de données (Licence) 6-33
Mise-à-jour automatique
Emp (Eno, Ename, Title, City)
Création automatique d’une valeur de clé
(autoincrément) :
CREATE TRIGGER SetEmpKey
BEFORE INSERT ON Emp
REFERENCING NEW AS N
FOR EACH ROW
BEGIN
N.Eno := SELECT COUNT(*) FROM Emp
END;
Cours Bases de données (Licence) 6-34
Mise-à-jour automatique
Pay(Title, Salary, Raise)
Maintenance des augmentations de salaire :
CREATE TRIGGER UpdateRaise
AFTER UPDATE OF Salary ON Pay
REFERENCING OLD AS O, NEW AS N
FOR EACH ROW
BEGIN
UPDATE Pay
SET Raise = N.Salary - O.Salary
WHERE Title = N.Title;
END
Cours Bases de données (Licence) 6-35
Analyse des triggers
Un trigger activé peut en activer un autre :
longues chaînes d'activation => problème de
performances
boucles d'activation => problème de terminaison
Recommandations :
pour l'intégrité, utiliser si possible le mécanisme des
contraintes plus facile à optimiser par le système.
associer les triggers à des règles de gestion.
Cours Bases de données (Licence) 6-36
