Dalibo SCOP
Formation | Module S2 |
Titre | Création d’objets et mises à jour |
Révision | 24.04 |
https://dali.bo/s2_pdf | |
EPUB | https://dali.bo/s2_epub |
HTML | https://dali.bo/s2_html |
Slides | https://dali.bo/s2_slides |
TP | https://dali.bo/s2_tp |
TP (solutions) | https://dali.bo/s2_solutions |
Vous trouverez en ligne les différentes versions complètes de ce document.
Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :
Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.
Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.
Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.
Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode
Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.
PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.
Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 12 à 16.
Sur les versions précédentes susceptibles d’être encore rencontrées en production, seuls quelques points très importants sont évoqués, en plus éventuellement de quelques éléments historiques.
Sauf précision contraire, le système d’exploitation utilisé est Linux.
Le module précédent nous a permis de voir comment lire des données à partir de requêtes SQL. Ce module a pour but de présenter la création et la gestion des objets dans la base de données (par exemple les tables), ainsi que l’ajout, la suppression et la modification de données.
Une dernière partie sera consacrée aux transactions.
Les ordres DDL (acronyme de Data Definition Language
)
permettent de créer des objets dans la base de données et notamment la
structure de base du standard SQL : les tables.
La norme SQL définit un certain nombre d’objets standards qu’il est possible de créer en utilisant les ordres DDL. D’autres types d’objets existent bien entendu, comme les domaines. Les ordres DDL permettent également de créer des index, bien qu’ils ne soient pas définis dans la norme SQL.
La seule structure de données possible dans une base de données relationnelle est la table.
La création d’objet passe généralement par l’ordre
CREATE
. La syntaxe dépend fortement du type d’objet. Voici
trois exemples :
CREATE SCHEMA s1;
CREATE TABLE t1 (c1 integer, c2 text);
CREATE SEQUENCE s1 INCREMENT BY 5 START 10;
Pour créer un objet, il faut être propriétaire du schéma ou de la
base auquel appartiendra l’objet ou avoir le droit CREATE
sur le schéma ou la base.
Modifier un objet veut dire modifier ses propriétés. On utilise dans
ce cas l’ordre ALTER
. Il faut être propriétaire de l’objet
pour pouvoir le faire.
Deux propriétés sont communes à tous les objets : le nom de l’objet et son propriétaire. Deux autres sont fréquentes et dépendent du type de l’objet : le schéma et le tablespace. Les autres propriétés dépendent directement du type de l’objet.
Seul un propriétaire peut supprimer un objet. Il utilise pour cela
l’ordre DROP
. Pour les objets ayant des dépendances,
l’option CASCADE
permet de tout supprimer d’un coup. C’est
très pratique, et c’est en même temps très dangereux : il faut donc
utiliser cette option à bon escient.
Si un objet dépendant de l’objet à supprimer a lui aussi une dépendance, sa dépendance sera également supprimée. Ainsi de suite jusqu’à la dernière dépendance.
La notion de schéma dans PostgreSQL est à rapprocher de la notion
d’espace de nommage (ou namespace) de certains langages de
programmation. Le catalogue système qui contient la définition des
schémas dans PostgreSQL s’appelle d’ailleurs
pg_namespace
.
Les schémas sont utilisés pour répartir les objets de façon logique, suivant un schéma interne à l’entreprise. Ils servent aussi à faciliter la gestion des droits (il suffit de révoquer le droit d’utilisation d’un schéma à un utilisateur pour que les objets contenus dans ce schéma ne soient plus accessibles à cet utilisateur).
Un schéma public
est créé par défaut dans toute nouvelle
base de données. Tout le monde a le droit d’y créer des objets. Il est
cependant possible de révoquer ce droit ou supprimer ce schéma.
L’ordre CREATE SCHEMA
permet de créer un schéma. Il
suffit de lui spécifier le nom du schéma. CREATE SCHEMA
offre d’autres possibilités qui sont rarement utilisées.
L’ordre
ALTER SCHEMA nom_schema RENAME TO nouveau_nom_schema
permet
de renommer un schéma. L’ordre
ALTER SCHEMA nom_schema OWNER TO proprietaire
permet de
donner un nouveau propriétaire au schéma.
Enfin, l’ordre DROP SCHEMA
permet de supprimer un
schéma. La clause IF EXISTS
permet d’éviter la levée d’une
erreur si le schéma n’existe pas (très utile dans les scripts SQL). La
clause CASCADE
permet de supprimer le schéma ainsi que tous
les objets qui sont positionnés dans le schéma.
Exemples
Création d’un schéma reference
:
CREATE SCHEMA reference;
Une table peut être créée dans ce schéma :
CREATE TABLE reference.communes (
commune text,char(5),
codepostal
departement text,integer
codeinsee );
La suppression directe du schéma ne fonctionne pas car il porte
encore la table communes
:
DROP SCHEMA reference;
drop schema reference because other objects depend on it
ERROR: cannot table reference.communes depends on schema reference
DETAIL: Use DROP ... CASCADE to drop the dependent objects too. HINT:
L’option CASCADE
permet de supprimer le schéma et ses
objets dépendants :
DROP SCHEMA reference CASCADE;
drop cascades to table reference.communes NOTICE:
Le paramètre search_path
permet de définir un chemin de
recherche pour pouvoir retrouver les tables dont le nom n’est pas
qualifié par le nom de son schéma. PostgreSQL procèdera de la même façon
que le système avec la variable $PATH
: il recherche la
table dans le premier schéma listé. S’il trouve une table portant ce nom
dans le schéma, il préfixe le nom de table avec celui du schéma. S’il ne
trouve pas de table de ce nom dans le schéma, il effectue la même
opération sur le prochain schéma de la liste du
search_path
. S’il n’a trouvé aucune table de ce nom dans
les schémas listés par search_path
, PostgreSQL lève une
erreur.
Comme beaucoup d’autres paramètres, le search_path
peut
être positionné à différents endroits. Par défaut, il est assigné à
$user, public
, c’est-à-dire que le premier schéma de
recherche portera le nom de l’utilisateur courant, et le second schéma
de recherche est public
.
On peut vérifier la variable search_path
à l’aide de la
commande SHOW
:
SHOW search_path;
search_path----------------
"$user",public
1 row) (
Pour obtenir une configuration particulière, la variable
search_path
peut être positionnée dans le fichier
postgresql.conf
:
= '"$user",public' search_path
Cette variable peut aussi être positionnée au niveau d’un
utilisateur. Chaque fois que l’utilisateur se connectera, il prendra le
search_path
de sa configuration spécifique :
ALTER ROLE nom_role SET search_path = "$user", public;
Cela peut aussi se faire au niveau d’une base de données. Chaque fois
qu’un utilisateur se connectera à la base, il prendra le
search_path
de cette base, sauf si l’utilisateur a déjà une
configuration spécifique :
ALTER DATABASE nom_base SET search_path = "$user", public;
La variable search_path
peut également être positionnée
pour un utilisateur particulier, dans une base particulière :
ALTER ROLE nom_role IN DATABASE nom_base SET search_path = "$user", public;
Enfin, la variable search_path
peut être modifiée
dynamiquement dans la session avec la commande SET
:
SET search_path = "$user", public;
Avant la version 9.3, les requêtes préparées et les fonctions
conservaient en mémoire le plan d’exécution des requêtes. Ce plan ne
faisait plus référence aux noms des objets mais à leurs identifiants. Du
coup, un search_path
changeant entre deux exécutions d’une
requête préparée ou d’une fonction ne permettait pas de cibler une table
différente. Voici un exemple le montrant :
-- création des objets
CREATE SCHEMA s1;
CREATE SCHEMA s2;
CREATE TABLE s1.t1 (c1 text);
CREATE TABLE s2.t1 (c1 text);
INSERT INTO s1.t1 VALUES('schéma s1');
INSERT INTO s2.t1 VALUES('schéma s2');
SELECT * FROM s1.t1;
c1-----------
schéma s11 row)
(
SELECT * FROM s2.t1;
c1-----------
schéma s21 row)
(
-- il y a bien des données différentes dans chaque table
SET search_path TO s1;
PREPARE req AS SELECT * FROM t1;
EXECUTE req;
c1-----------
schéma s11 row)
(
SET search_path TO s2;
EXECUTE req;
c1-----------
schéma s11 row)
(
-- malgré le changement de search_path, nous en sommes toujours
-- aux données de l'autre table
=# SELECT * FROM t1;
b1
c1-----------
schéma s21 row) (
Dans ce cas, il est préférable de configurer le paramètre
search_path
directement au niveau de la fonction.
À partir de la version 9.3, dès que le search_path
change, les plans en cache sont supprimés (dans le cas de la fonction)
ou recréés (dans le cas des requêtes préparées).
Les séquences sont des objets standards qui permettent de générer des séquences de valeur. Elles sont utilisées notamment pour générer un numéro unique pour un identifiant ou, plus rarement, pour disposer d’un compteur informatif, mis à jour au besoin.
Le cache de la séquence a pour effet de générer un certain nombre de valeurs en mémoire afin de les mettre à disposition de la session qui a utilisé la séquence. Même si les valeurs pré-calculées ne sont pas consommées dans la session, elles seront consommées au niveau de la séquence. Cela peut avoir pour effet de créer des trous dans les séquences d’identifiants et de consommer très rapidement les numéros de séquence possibles. Le cache de séquence n’a pas besoin d’être ajusté sur des applications réalisant de petites transactions. Il permet en revanche d’améliorer les performances sur des applications qui utilisent massivement des numéros de séquences, notamment pour réaliser des insertions massives.
La syntaxe complète est donnée dans le slide.
Le mot clé TEMPORARY
ou TEMP
permet de
définir si la séquence est temporaire. Si tel est le cas, elle sera
détruite à la déconnexion de l’utilisateur.
Le mot clé INCREMENT
définit l’incrément de la séquence,
MINVALUE
, la valeur minimale de la séquence et
MAXVALUE
, la valeur maximale. START
détermine
la valeur de départ initiale de la séquence, c’est-à-dire juste après sa
création. La clause CACHE
détermine le cache de séquence.
CYCLE
permet d’indiquer au SGBD que la séquence peut
reprendre son compte à MINVALUE
lorsqu’elle aura atteint
MAXVALUE
. La clause NO CYCLE
indique que le
rebouclage de la séquence est interdit, PostgreSQL lèvera alors une
erreur lorsque la séquence aura atteint son MAXVALUE
.
Enfin, la clause OWNED BY
détermine l’appartenance d’une
séquence à une colonne d’une table. Ainsi, si la colonne est supprimée,
la séquence sera implicitement supprimée.
Exemple de séquence avec rebouclage :
CREATE SEQUENCE testseq INCREMENT BY 1 MINVALUE 3 MAXVALUE 5 CYCLE START WITH 4;
SELECT nextval('testseq');
nextval---------
4
SELECT nextval('testseq');
nextval---------
5
SELECT nextval('testseq');
nextval---------
3
Les propriétés de la séquence peuvent être modifiés avec l’ordre
ALTER SEQUENCE
.
La séquence peut être affectée à un nouveau propriétaire :
ALTER SEQUENCE [ IF EXISTS ] nom OWNER TO nouveau_propriétaire
Elle peut être renommée :
ALTER SEQUENCE [ IF EXISTS ] nom RENAME TO nouveau_nom
Enfin, elle peut être positionnée dans un nouveau schéma :
ALTER SEQUENCE [ IF EXISTS ] nom SET SCHEMA nouveau_schema
Voici la syntaxe complète de DROP SEQUENCE
:
DROP SEQUENCE [ IF EXISTS ] nom [, ...] [ CASCADE | RESTRICT ]
Le mot clé CASCADE
permet de supprimer la séquence ainsi
que tous les objets dépendants (par exemple la valeur par défaut d’une
colonne).
La fonction nextval()
permet d’obtenir le numéro de
séquence suivant. Son comportement n’est pas transactionnel. Une fois
qu’un numéro est consommé, il n’est pas possible de revenir dessus,
malgré un ROLLBACK
de la transaction. La séquence est le
seul objet à avoir un comportement de ce type.
La fonction currval()
permet d’obtenir le numéro de
séquence courant, mais son usage nécessite d’avoir utilisé
nextval()
dans la session.
Il est possible d’interroger une séquence avec une requête
SELECT
. Cela permet d’obtenir des informations sur la
séquence, dont la dernière valeur utilisée dans la colonne
last_value
. Cet usage n’est pas recommandé en production et
doit plutôt être utilisé à titre informatif.
Exemples
Utilisation d’une séquence simple :
CREATE SEQUENCE testseq
INCREMENT BY 1 MINVALUE 10 MAXVALUE 20 START WITH 15 CACHE 1;
SELECT currval('testseq');
of sequence "testseq" is not yet defined in this session
ERROR: currval
SELECT * FROM testseq ;
- [ RECORD 1 ]-+--------
sequence_name | testseqlast_value | 15
15
start_value | 1
increment_by | 20
max_value | 10
min_value | 5
cache_value | 0
log_cnt |
is_cycled | f
is_called | f
SELECT nextval('testseq');
nextval---------
15
1 row)
(
SELECT currval('testseq');
currval---------
15
SELECT nextval('testseq');
nextval---------
16
1 row)
(
ALTER SEQUENCE testseq RESTART WITH 5;
value (5) cannot be less than MINVALUE (10)
ERROR: RESTART
DROP SEQUENCE testseq;
Utilisation d’une séquence simple avec cache :
CREATE SEQUENCE testseq INCREMENT BY 1 CACHE 10;
SELECT nextval('testseq');
nextval---------
1
Déconnexion et reconnexion de l’utilisateur :
SELECT nextval('testseq');
nextval---------
11
Suppression en cascade d’une séquence :
CREATE TABLE t2 (id serial);
\d t2Table "s2.t2"
Column | Type | Modifiers
--------+---------+-------------------------------------------------
id | integer | not null default nextval('t2_id_seq'::regclass)
DROP SEQUENCE t2_id_seq;
drop sequence t2_id_seq because other objects depend on it
ERROR: cannot default for table t2 column id depends on sequence t2_id_seq
DETAIL: Use DROP ... CASCADE to drop the dependent objects too.
HINT:
DROP SEQUENCE t2_id_seq CASCADE;
drop cascades to default for table t2 column id
NOTICE:
\d t2Table "s2.t2"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
Certaines bases de données offrent des colonnes auto-incrémentées
(autoincrement
de MySQL ou identity
de SQL
Server).
PostgreSQL ne possède identity
qu’à partir de la v 10.
Jusqu’en 9.6 on pourra utiliser serial
un équivalent qui
s’appuie sur les séquences et la possibilité d’appliquer une valeur par
défaut à une colonne.
Par exemple, si l’on crée la table suivante :
CREATE TABLE exemple_serial (
id SERIAL PRIMARY KEY,
INTEGER NOT NULL
valeur );
On s’aperçoit que la table a été créée telle que demandé, mais qu’une
séquence a aussi été créée. Elle porte un nom dérivé de la table associé
à la colonne correspondant au type serial
, terminé par
seq
:
=# \d
postgresList of relations
Schema | Name | Type | Owner
--------+-----------------------+----------+--------
public | exemple_serial | table | thomas
public | exemple_serial_id_seq | sequence | thomas
En examinant plus précisément la définition de la table, on
s’aperçoit que la colonne id
porte une valeur par défaut
qui correspond à l’appel de la fonction nextval()
sur la
séquence qui a été créée implicitement :
=# \d exemple_serial
postgresTable "public.exemple_serial"
Column | Type | Modifiers
--------+---------+-------------------------------------------------------------
id | integer | not null default nextval('exemple_serial_id_seq'::regclass)
integer | not null
valeur | Indexes:
"exemple_serial_pkey" PRIMARY KEY, btree (id)
smallserial
et bigserial
sont des variantes
de serial
s’appuyant sur des types d’entiers plus courts ou
plus longs.
Un domaine est utilisé pour définir un type utilisateur qui est en fait un type utilisateur standard accompagné de la définition de contraintes particulières.
Les domaines sont utiles pour ramener la définition de contraintes communes à plusieurs colonnes sur un seul objet. La maintenance en est ainsi facilitée.
L’ordre CREATE DOMAIN
permet de créer un domaine,
ALTER DOMAIN
permet de modifier sa définition, et enfin,
DROP DOMAIN
permet de supprimer un domaine.
Exemples
Gestion d’un domaine salaire
:
-- ajoutons le domaine et la table
CREATE DOMAIN salaire AS integer CHECK (VALUE > 0);
CREATE TABLE employes (id serial, nom text, paye salaire);
\d employesTable « public.employes »
Type | NULL-able | Par défaut
Colonne | ---------+---------+------------+--------------------------------------
id | integer | not null | nextval('employes_id_seq'::regclass)
nom | text | |
paye | salaire | |
-- insérons des données dans la nouvelle table
INSERT INTO employes (nom, paye) VALUES ('Albert', 1500);
INSERT INTO employes (nom, paye) VALUES ('Alphonse', 0);
value for domain salaire violates check constraint "salaire_check"
ERROR: -- erreur logique vu qu'on ne peut avoir qu'un entier strictement positif
INSERT INTO employes (nom, paye) VALUES ('Alphonse', 1000);
INSERT 0 1
INSERT INTO employes (nom, paye) VALUES ('Bertrand', NULL);
INSERT 0 1
-- tous les employés doivent avoir un salaire
-- il faut donc modifier la contrainte, pour s'assurer
-- qu'aucune valeur NULL ne soit saisi
ALTER DOMAIN salaire SET NOT NULL;
column "paye" of table "employes" contains null values
ERROR: -- la ligne est déjà présente, il faut la modifier
UPDATE employes SET paye=1500 WHERE nom='Bertrand';
-- maintenant, on peut ajouter la contrainte au domaine
ALTER DOMAIN salaire SET NOT NULL;
INSERT INTO employes (nom, paye) VALUES ('Delphine', NULL);
domain salaire does not allow null values
ERROR: -- la contrainte est bien vérifiée
-- supprimons maintenant la contrainte
DROP DOMAIN salaire;
drop type salaire because other objects depend on it
ERROR: cannot table employes column paye depends on type salaire
DETAIL: Use DROP ... CASCADE to drop the dependent objects too.
HINT: -- il n'est pas possible de supprimer le domaine car il est référencé dans une
-- table. Il faut donc utiliser l'option CASCADE
=# DROP DOMAIN salaire CASCADE;
b1drop cascades to table employes column paye
NOTICE: DROP DOMAIN
-- le domaine a été supprimée ainsi que toutes les colonnes ayant ce type
\d employesTable « public.employes »
Type | NULL-able | Par défaut
Colonne | ---------+---------+-----------+--------------------------------------
id | integer | not null | nextval('employes_id_seq'::regclass)
nom | text | |
Création et utilisation d’un domaine
code_postal_us
:
CREATE DOMAIN code_postal_us AS TEXT
CHECK(
VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);
CREATE TABLE courrier_us (
PRIMARY KEY,
id_adresse SERIAL NOT NULL,
rue1 TEXT
rue2 TEXT,
rue3 TEXT,NOT NULL,
ville TEXT NOT NULL
code_postal code_postal_us
);
INSERT INTO courrier_us (rue1,ville,code_postal)
VALUES ('51 Franklin Street', 'Boston, MA', '02110-1335' );
INSERT 0 1
INSERT INTO courrier_us (rue1,ville,code_postal)
VALUES ('10 rue d''Uzès','Paris','F-75002') ;
ERREUR: la valeur pour le domaine code_postal_us viole la contrainte de vérification « code_postal_us_check »
La table est l’élément de base d’une base de données. Elle est composée de colonnes (à sa création) et est remplie avec des enregistrements (lignes de la table). Sa définition peut aussi faire intervenir des contraintes, qui sont au niveau table ou colonne.
Pour créer une table, il faut donner son nom et la liste des colonnes. Une colonne est définie par son nom et son type, mais aussi des contraintes optionnelles.
Des options sont possibles pour les tables, comme les clauses de stockage. Dans ce cas, on sort du contexte logique pour se placer au niveau physique.
La création d’une table passe par l’ordre CREATE TABLE
.
La définition des colonnes et des contraintes sont entre parenthèse
après le nom de la table.
Les colonnes sont indiquées l’une après l’autre, en les séparant par des virgules.
Deux informations sont obligatoires pour chaque colonne : le nom et le type de la colonne. Dans le cas d’une colonne contenant du texte, il est possible de fournir le collationnement de la colonne. Quelque soit la colonne, il est ensuite possible d’ajouter des contraintes.
La clause DEFAULT
permet d’affecter une valeur par
défaut lorsqu’une colonne n’est pas référencée dans l’ordre d’insertion
ou si une mise à jour réinitialise la valeur de la colonne à sa valeur
par défaut.
Les types sériés définissent une valeur par défaut sur les colonnes
de ce type. Cette valeur est le retour de la fonction
nextval()
sur la séquence affectée automatiquement à cette
colonne.
Exemples
Assignation d’une valeur par défaut :
CREATE TABLE valdefaut (
id integer,
integer DEFAULT 0,
i integer DEFAULT 0
j
);
INSERT INTO valdefaut (id, i) VALUES (1, 10);
SELECT * FROM valdefaut ;
id | i | j
----+----+---
1 | 10 | 0
1 row) (
L’ordre CREATE TABLE
permet également de créer une table
à partir de la définition d’une table déjà existante en utilisant la
clause LIKE
en lieu et place de la définition habituelles
des colonnes. Par défaut, seule la définition des colonnes avec leur
typage est repris.
Les clauses INCLUDING
permettent de récupérer d’autres
éléments de la définition de la table, comme les valeurs par défaut
(INCLUDING DEFAULTS
), les contraintes d’intégrité
(INCLUDING CONSTRAINTS
), les index
(INCLUDING INDEXES
), les clauses de stockage
(INCLUDING STORAGE
) ainsi que les commentaires
(INCLUDING COMMENTS
). Si l’ensemble de ces éléments sont
repris, il est possible de résumer la clause INCLUDING
à
INCLUDING ALL
.
La clause CREATE TABLE
suivante permet de créer une
table archive_evenements_2010
à partir de la définition de
la table evenements
:
CREATE TABLE archive_evenements_2010
LIKE evenements
(INCLUDING DEFAULTS
INCLUDING CONSTRAINTS
INCLUDING INDEXES
INCLUDING STORAGE
INCLUDING COMMENTS
);
Elle est équivalente à :
CREATE TABLE archive_evenements_2010
LIKE evenements
(INCLUDING ALL
);
Pour modifier la définition d’une table (et non pas son contenu), il
convient d’utiliser l’ordre ALTER TABLE
. Il permet de
traiter la définition de la table (nom, propriétaire, schéma, liste des
colonnes), la définition des colonnes (ajout, modification de nom et de
type, suppression… mais pas de changement au niveau de leur ordre), et
la définition des contraintes (ajout et suppression).
Suivant l’opération réalisée, les verrous posés ne seront pas les
mêmes, même si le verrou par défaut sera un verrou exclusif. Par
exemple, renommer une table nécessite un verrou exclusif mais changer la
taille de l’échantillon statistiques bloque uniquement certaines
opérations de maintenance (comme VACUUM
et
ANALYZE
) et certaines opérations DDL. Il convient donc
d’être très prudent lors de l’utilisation de la commande
ALTER TABLE
sur un serveur en production.
Certaines opérations nécessitent de vérifier les données. C’est
évident lors de l’ajout d’une contrainte (comme une clé primaire ou une
contrainte NOT NULL), mais c’est aussi le cas lors d’un changement de
type de données. Passer une colonne du type text
vers le
type timestamp
nécessite de vérifier que les données de
cette colonne ne contiennent que des données convertibles vers le type
timestamp
. Dans les anciennes versions, la vérification
était effectuée en permanence, y compris pour des cas simples où cela
n’était pas nécessaire. Par exemple, convertir une colonne du type
varchar(200)
à varchar(100)
nécessite de
vérifier que la colonne ne contient que des chaînes de caractères de
longueur inférieure à 100. Mais convertir une colonne du type
varchar(100)
vers le type varchar(200)
ne
nécessite pas de vérification. Les dernières versions de PostgreSQL font
la différence, ce qui permet d’éviter de perdre du temps pour une
vérification inutile.
Certaines opérations nécessitent une réécriture de la table. Par
exemple, convertir une colonne de type varchar(5)
vers le
type int4
impose une réécriture de la table car il n’y a
pas de compatibilité binaire entre les deux types. Ce n’est pas le cas
si la modification est uniquement sur la taille d’une colonne
varchar
. Certaines optimisations sont ajoutées sur les
nouvelles versions de PostgreSQL. Par exemple, l’ajout d’une colonne
avec une valeur par défaut causait la réécriture complète de la table
pour intégrer la valeur de cette nouvelle colonne alors que l’ajout
d’une colonne sans valeur par défaut n’avait pas la même conséquence. À
partir de la version 11, cette valeur par défaut est enregistrée dans la
colonne attmissingval
du catalogue système
pg_attribute
et la table n’a de ce fait plus besoin d’être
réécrite.
Il convient donc d’être très prudent lors de l’utilisation de la
commande ALTER TABLE
. Elle peut poser des problèmes de
performances, à cause de verrous posés par d’autres commandes, de
verrous qu’elle réclame, de vérification des données, voire de
réécriture de la table.
L’ordre DROP TABLE
permet de supprimer une table.
L’ordre DROP TABLE ... CASCADE
permet de supprimer une
table ainsi que tous ses objets dépendants. Il peut s’agir de séquences
rattachées à une colonne d’une table, à des colonnes référençant la
table à supprimer, etc.
Les données dans les différentes tables ne sont pas indépendantes mais obéissent à des règles sémantiques mises en place au moment de la conception du modèle conceptuel des données. Les contraintes d’intégrité ont pour principal objectif de garantir la cohérence des données entre elles, et donc de veiller à ce qu’elles respectent ces règles sémantiques. Si une insertion, une mise à jour ou une suppression viole ces règles, l’opération est purement et simplement annulée.
Une clé primaire permet d’identifier une ligne de façon unique, il n’en existe qu’une seule par table.
Une clé primaire garantit que toutes les valeurs de la ou des colonnes qui composent cette clé sont uniques et non nulles. Elle peut être composée d’une seule colonne ou de plusieurs colonnes, selon le besoin.
La clé primaire est déterminée au moment de la conception du modèle de données.
Les clés primaires créent implicitement un index qui permet de renforcer cette contrainte.
Exemples
Définition de la table region
:
CREATE TABLE region
(id serial UNIQUE NOT NULL,
NOT NULL,
libelle text
PRIMARY KEY(id)
);
INSERT INTO region VALUES (1, 'un');
INSERT INTO region VALUES (2, 'deux');
INSERT INTO region VALUES (NULL, 'trois');
null value in column "id" violates not-null constraint
ERROR: row contains (null, trois).
DETAIL: Failing
INSERT INTO region VALUES (1, 'trois');
key value violates unique constraint "region_pkey"
ERROR: duplicate Key (id)=(1) already exists.
DETAIL:
INSERT INTO region VALUES (3, 'trois');
SELECT * FROM region;
id | libelle
----+---------
1 | un
2 | deux
3 | trois
3 rows) (
Une contrainte d’unicité permet de garantir que les valeurs de la ou
des colonnes sur lesquelles porte la contrainte sont uniques. Elle
autorise néanmoins d’avoir plusieurs valeurs NULL
car elles
ne sont pas considérées comme égales mais de valeur inconnue
(UNKNOWN
).
Une contrainte d’unicité peut être créée simplement en créant un
index UNIQUE
approprié. Ceci est fortement déconseillé du
fait que la contrainte ne sera pas référencée comme telle dans le schéma
de la base de données. Il sera donc très facile de ne pas la remarquer
au moment d’une reprise du schéma pour une évolution majeure de
l’application. Une colonne possédant un index UNIQUE
peut
malgré tout être référencée par une clé étrangère.
Les contraintes d’unicité créent implicitement un index qui permet de renforcer cette unicité.
Voici un exemple complet.
Sans contrainte d’unicité, on peut insérer plusieurs fois la même valeur :
=# CREATE TABLE utilisateurs(id integer);
postgresCREATE TABLE
=# INSERT INTO utilisateurs VALUES (10);
postgresINSERT 0 1
=# INSERT INTO utilisateurs VALUES (10);
postgresINSERT 0 1
Ce n’est plus le cas avec une contrainte d’unicité :
=# TRUNCATE utilisateurs;
postgresTRUNCATE TABLE
=# ALTER TABLE utilisateurs ADD UNIQUE(id);
postgresALTER TABLE
=# INSERT INTO utilisateurs (id) VALUES (10);
postgresINSERT 0 1
=# INSERT INTO utilisateurs (id) VALUES (10);
postgreskey value violates unique constraint "utilisateurs_id_key"
ERROR: duplicate Key (id)=(10) already exists.
DETAIL: =# INSERT INTO utilisateurs (id) VALUES (11);
postgresINSERT 0 1
Par contre, on peut insérer plusieurs valeurs NULL :
=# INSERT INTO utilisateurs (id) VALUES (NULL);
postgresINSERT 0 1
=# INSERT INTO utilisateurs (id) VALUES (NULL);
postgresINSERT 0 1
=# INSERT INTO utilisateurs (id) VALUES (NULL);
postgresINSERT 0 1
Ce comportement est modifiable en version 15. Lors de la création de la contrainte, il faut préciser ce nouveau comportement :
=# TRUNCATE utilisateurs;
postgresTRUNCATE TABLE
=# ALTER TABLE utilisateurs DROP CONSTRAINT utilisateurs_id_key;
postgresALTER TABLE
=# ALTER TABLE utilisateurs ADD UNIQUE NULLS NOT DISTINCT(id);
postgresALTER TABLE
=# INSERT INTO utilisateurs (id) VALUES (10);
postgresINSERT 0 1
=# INSERT INTO utilisateurs (id) VALUES (10);
postgreskey value violates unique constraint "utilisateurs_id_key"
ERROR: duplicate Key (id)=(10) already exists.
DETAIL: =# INSERT INTO utilisateurs (id) VALUES (11);
postgresINSERT 0 1
=# INSERT INTO utilisateurs (id) VALUES (NULL);
postgresINSERT 0 1
=# INSERT INTO utilisateurs (id) VALUES (NULL);
postgreskey value violates unique constraint "utilisateurs_id_key"
ERROR: duplicate Key (id)=(null) already exists. DETAIL:
Une clé étrangère sur une table fait référence à une clé primaire ou une contrainte d’unicité d’une autre table. La clé étrangère garantit que les valeurs des colonnes de cette clé existent également dans la table portant la clé primaire ou la contrainte d’unicité. On parle de contrainte référentielle d’intégrité : la contrainte interdit les valeurs qui n’existent pas dans la table référencée.
À titre d’exemple nous allons utiliser la base cave
.
La base cave (dump de 2,6 Mo, pour 71 Mo sur le disque au final) peut être téléchargée et restaurée ainsi :
curl -kL https://dali.bo/tp_cave -o cave.dump
psql -c "CREATE ROLE caviste LOGIN PASSWORD 'caviste'"
psql -c "CREATE DATABASE cave OWNER caviste"
pg_restore -d cave cave.dump
# NB : une erreur sur un schéma 'public' existant est normale
Le schéma suivant montre les différentes tables de la base :
Ainsi, la base cave définit une table region
et une
table appellation
. Une appellation d’origine est liée au
terroir, et par extension à son origine géographique. La table
appellation
est donc liée par une clé étrangère à la table
region
: la colonne region_id
de la table
appellation
référence la colonne id
de la
table region
.
Cette contrainte permet d’empêcher les utilisateurs d’entrer dans la
table appellation
des identifiants de région
(region_id
) qui n’existent pas dans la table
region
.
Exemples
Définition de la table stock
:
CREATE TABLE stock
(int not null,
vin_id int not null,
contenant_id not null,
annee int4 not null,
nombre int4
PRIMARY KEY(vin_id,contenant_id,annee),
FOREIGN KEY(vin_id) REFERENCES vin(id) ON DELETE CASCADE,
FOREIGN KEY(contenant_id) REFERENCES contenant(id) ON DELETE CASCADE
);
Création d’une table mère et d’une table fille. La table fille possède une clé étrangère qui référence la table mère :
CREATE TABLE mere (id integer, t text);
CREATE TABLE fille (id integer, mere_id integer, t text);
ALTER TABLE mere ADD CONSTRAINT pk_mere PRIMARY KEY (id);
ALTER TABLE fille
ADD CONSTRAINT fk_mere_fille
FOREIGN KEY (mere_id)
REFERENCES mere (id)
FULL
MATCH ON UPDATE NO ACTION
ON DELETE CASCADE;
INSERT INTO mere (id, t) VALUES (1, 'val1'), (2, 'val2');
-- l'ajout de données dans la table fille qui font bien référence
-- à la table mere fonctionne
INSERT INTO fille (id, mere_id, t) VALUES (1, 1, 'val1');
INSERT INTO fille (id, mere_id, t) VALUES (2, 2, 'val2');
-- l'ajout de données dans la table fille qui ne font pas référence
-- à la table mere est annulé
INSERT INTO fille (id, mere_id, t) VALUES (3, 3, 'val3');
insert or update on table "fille" violates foreign key constraint
ERROR: "fk_mere_fille"
Key (mere_id)=(3) is not present in table "mere".
DETAIL:
=# SELECT * FROM fille;
b1id | mere_id | t
----+---------+------
1 | 1 | val1
2 | 2 | val2
2 rows)
(
-- mettre à jour la référence dans la table mere ne fonctionnera pas
-- car la contrainte a été définie pour refuser les mises à jour
-- (ON UPDATE NO ACTION)
=# UPDATE mere SET id=3 WHERE id=2;
b1update or delete on table "mere" violates foreign key constraint
ERROR: "fk_mere_fille" on table "fille"
Key (id)=(2) is still referenced from table "fille".
DETAIL:
-- par contre, la suppression d'une ligne de la table mere référencée dans la
-- table fille va propager la suppression jusqu'à la table fille
-- (ON DELETE CASCADE)
=# DELETE FROM mere WHERE id=2;
b1DELETE 1
=# SELECT * FROM fille;
b1id | mere_id | t
----+---------+------
1 | 1 | val1
1 row)
(
=# SELECT * FROM mere;
b1id | t
----+------
1 | val1
1 row) (
La directive MATCH
permet d’indiquer si la contrainte
doit être entièrement vérifiée (MATCH FULL
) ou si la clé
étrangère autorise des valeurs NULL
(MATCH SIMPLE
). MATCH SIMPLE
est la valeur par
défaut.
Avec MATCH FULL
, toutes les valeurs des colonnes qui
composent la clé étrangère de la table référençant doivent avoir une
correspondance dans la table référencée.
Avec MATCH SIMPLE
, les valeurs des colonnes qui
composent la clé étrangère de la table référençant peuvent comporter des
valeurs NULL
. Dans le cas des clés étrangères
multi-colonnes, toutes les colonnes peuvent ne pas être renseignées.
Dans le cas des clés étrangères sur une seule colonne, la contrainte
autorise les valeurs NULL
.
Exemples
Les exemples reprennent les tables mere
et
fille
créées plus haut.
INSERT INTO fille VALUES (4, NULL, 'test');
SELECT * FROM fille;
id | mere_id | t
----+---------+------
1 | 1 | val1
2 | 2 | val2
4 | | test
2 rows) (
Cette contrainte permet d’avoir une colonne dont la valeur est
incrémentée automatiquement, soit en permanence (clause
ALWAYS
), soit quand aucune valeur n’est saisie (clause
BY DEFAULT
). Cette technique d’auto-incrémentation
correspond au standard SQL, contrairement au pseudo-type
serial
qui était utilisé jusqu’à la version 10.
De plus, elle corrige certains défauts de ce pseudo-type. Avec le
type serial
, l’utilisation de
CREATE TABLE .. LIKE
copiait la contrainte de valeur par
défaut sans changer le nom de la séquence. Il n’est pas possible
d’ajouter ou de supprimer un pseudo-type serial
avec
l’instruction ALTER TABLE
. La suppression de la contrainte
DEFAULT
d’un type serial
ne supprime pas la
séquence associée. Tout ceci fait que la définition d’une colonne
d’identité est préférable à l’utilisation du pseudo-type
serial
.
Il reste obligatoire de définir une clé primaire ou unique si l’on
tient à l’unicité des valeurs car même une clause
GENERATED ALWAYS AS IDENTITY
peut être contournée avec une
mise à jour portant la mention OVERRIDING SYSTEM VALUE
.
Exemple :
CREATE table personnes (id int GENERATED ALWAYS AS IDENTITY, nom TEXT);
CREATE TABLE
INSERT INTO personnes (nom) VALUES ('Dupont') ;
INSERT 0 1
INSERT INTO personnes (nom) VALUES ('Durand') ;
INSERT 0 1
SELECT * FROM personnes ;
id | nom
----+--------
1 | Dupont
2 | Durand
2 lignes)
(
INSERT INTO personnes (id,nom) VALUES (3,'Martin') ;
insert into column "id"
ERROR: cannot Column "id" is an identity column defined as GENERATED ALWAYS.
DÉTAIL : Use OVERRIDING SYSTEM VALUE to override.
ASTUCE :
INSERT INTO personnes (id,nom) OVERRIDING SYSTEM VALUE VALUES (3,'Martin') ;
INSERT 0 1
INSERT INTO personnes (id,nom) OVERRIDING SYSTEM VALUE VALUES (3,'Dupond') ;
INSERT 0 1
SELECT * FROM personnes ;
id | nom
----+--------
1 | Dupont
2 | Durand
3 | Martin
3 | Dupond
Si des valeurs d’une clé primaire sont mises à jour ou supprimées, cela peut entrainer des incohérences dans la base de données si des valeurs de clés étrangères font référence aux valeurs de la clé primaire touchées par le changement.
Afin de pouvoir gérer cela, la norme SQL prévoit plusieurs
comportements possibles. La clause ON UPDATE
permet de
définir comment le SGBD va réagir si la clé primaire référencée est mise
à jour. La clause ON DELETE
fait de même pour les
suppressions.
Les actions possibles sont :
NO ACTION
(ou RESTRICT
), qui produit une
erreur si une ligne référence encore le ou les lignes touchées par le
changement ;CASCADE
, pour laquelle la mise à jour ou la suppression
est propagée aux valeurs référençant le ou les lignes touchées par le
changement ;SET NULL
, la valeur de la colonne devient
NULL
;SET DEFAULT
, pour lequel la valeur de la colonne prend
la valeur par défaut de la colonne.Le comportement par défaut est NO ACTION
, ce qui est
habituellement recommandé pour éviter les suppressions en chaîne mal
maîtrisées.
Exemples
Les exemples reprennent les tables mere
et
fille
créées plus haut.
Tentative d’insertion d’une ligne dont la valeur de
mere_id
n’existe pas dans la table mere
:
INSERT INTO fille (id, mere_id, t) VALUES (1, 3, 'val3');
insert or update on table "fille" violates foreign key constraint
ERROR: "fk_mere_fille"
Key (mere_id)=(3) is not present in table "mere". DETAIL:
Mise à jour d’une ligne de la table mere
pour modifier
son id
. La clé étrangère est déclarée
ON UPDATE NO ACTION
, donc la mise à jour devrait être
interdite :
UPDATE mere SET id = 3 WHERE id = 1;
update or delete on table "mere" violates foreign key constraint
ERROR: "fk_mere_fille" on table "fille"
Key (id)=(1) is still referenced from table "fille". DETAIL:
Suppression d’une ligne de la table mere
. La clé
étrangère sur fille
est déclarée
ON DELETE CASCADE
, la suppression sera donc propagée aux
tables qui référencent la table mere
:
DELETE FROM mere WHERE id = 1;
SELECT * FROM fille ;
id | mere_id | t
----+---------+------
2 | 2 | val2
1 row) (
La clause NOT NULL
permet de s’assurer que la valeur de
la colonne portant cette contrainte est renseignée. Dit autrement, elle
doit obligatoirement être renseignée. Par défaut, la colonne peut avoir
une valeur NULL
, donc n’est pas obligatoirement
renseignée.
La clause CHECK
spécifie une expression de résultat
booléen que les nouvelles lignes ou celles mises à jour doivent
satisfaire pour qu’une opération d’insertion ou de mise à jour
réussisse. Les expressions de résultat TRUE
ou
UNKNOWN
réussissent. Si une des lignes de l’opération
d’insertion ou de mise à jour produit un résultat FALSE
,
une exception est levée et la base de données n’est pas modifiée. Une
contrainte de vérification sur une colonne ne fait référence qu’à la
valeur de la colonne tandis qu’une contrainte sur la table fait
référence à plusieurs colonnes.
Actuellement, les expressions CHECK
ne peuvent ni
contenir des sous-requêtes ni faire référence à des variables autres que
les colonnes de la ligne courante. C’est techniquement réalisable, mais
non supporté.
Par défaut, toutes les contraintes d’intégrité sont vérifiées lors de l’exécution de chaque ordre SQL de modification, y compris dans une transaction. Cela peut poser des problèmes de cohérences de données : insérer dans une table fille alors qu’on n’a pas encore inséré les données dans la table mère, la clé étrangère de la table fille va rejeter l’insertion et annuler la transaction.
Le moment où les contraintes sont vérifiées est modifiable
dynamiquement par l’ordre SET CONSTRAINTS
:
SET CONSTRAINTS { ALL | nom [, ...] } { DEFERRED | IMMEDIATE }
mais ce n’est utilisable que pour les contraintes déclarées comme déferrables.
Voici quelques exemples :
mere
et
fille
=# BEGIN;
b1UPDATE mere SET id=3 where id=1;
update or delete on table "mere" violates foreign key constraint
ERROR: "fk_mere_fille" on table "fille"
Key (id)=(1) is still referenced from table "fille". DETAIL:
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
UPDATE mere SET id=3 WHERE id=1;
update or delete on table "mere" violates foreign key constraint
ERROR: "fk_mere_fille" on table "fille"
Key (id)=(1) is still referenced from table "fille". DETAIL:
CREATE TABLE mere (id integer, t text);
CREATE TABLE fille (id integer, mere_id integer, t text);
ALTER TABLE mere ADD CONSTRAINT pk_mere PRIMARY KEY (id);
ALTER TABLE fille
ADD CONSTRAINT fk_mere_fille
FOREIGN KEY (mere_id)
REFERENCES mere (id)
FULL
MATCH ON UPDATE NO ACTION
ON DELETE CASCADE
DEFERRABLE;
INSERT INTO mere (id, t) VALUES (1, 'val1'), (2, 'val2');
INSERT INTO fille (id, mere_id, t) VALUES (1, 1, 'val1');
INSERT INTO fille (id, mere_id, t) VALUES (2, 2, 'val2');
BEGIN;
SET CONSTRAINTS all deferred;
UPDATE mere SET id=3 WHERE id=1;
SELECT * FROM mere;
id | t
----+------
2 | val2
3 | val1
2 rows)
(
SELECT * FROM fille;
id | mere_id | t
----+---------+------
1 | 1 | val1
2 | 2 | val2
2 rows)
(
UPDATE fille SET mere_id=3 WHERE mere_id=1;
COMMIT;
Les contraintes d’intégrités du SGBD ne permettent pas d’exprimer une contrainte qui porte sur plusieurs tables ou simplement si sa vérification nécessite une sous-requête. Dans ce cas là, il est nécessaire d’écrire un trigger spécifique qui sera déclenché après chaque modification pour valider la contrainte.
Il ne faut toutefois pas systématiser l’utilisation de triggers pour valider des contraintes d’intégrité. Cela aurait un fort impact sur les performances et sur la maintenabilité de la base de données. Il vaut mieux privilégier les contraintes déclaratives et n’envisager l’emploi de triggers que dans les cas où ils sont réellement nécessaires.
L’ordre SELECT
permet de lire une ou plusieurs tables.
Les mises à jours utilisent des ordres distincts.
L’ordre INSERT
permet d’ajouter ou insérer des données
dans une table. L’ordre UPDATE
permet de modifier des
lignes déjà existantes. Enfin, l’ordre DELETE
permet de
supprimer des lignes. Ces ordres ne peuvent travailler que sur une seule
table à la fois. Si on souhaite par exemple insérer des données dans
deux tables, il est nécessaire de réaliser deux INSERT
distincts.
L’ordre INSERT
insère de nouvelles lignes dans une
table. Il permet d’insérer une ou plusieurs lignes spécifiées par les
expressions de valeur, ou zéro ou plusieurs lignes provenant d’une
requête.
La liste des noms de colonnes est optionnelle. Si elle n’est pas
spécifiée, alors PostgreSQL utilisera implicitement la liste de toutes
les colonnes de la table dans l’ordre de leur déclaration, ou les
N
premiers noms de colonnes si seules N
valeurs de colonnes sont fournies dans la clause VALUES
ou
dans la requête. L’ordre des noms des colonnes dans la liste n’a pas
d’importance particulière, il suffit de nommer les colonnes mises à
jour.
Chaque colonne absente de la liste, implicite ou explicite, se voit
attribuer sa valeur par défaut, s’il y en a une ou NULL
dans le cas contraire. Les expressions de colonnes qui ne correspondent
pas au type de données déclarées sont transtypées automatiquement, dans
la mesure du possible.
La clause VALUES
permet de définir une liste
d’expressions qui va constituer la ligne à insérer dans la base de
données. Les éléments de cette liste d’expression sont séparés par une
virgule. Cette liste d’expression est composée de constantes ou d’appels
à des fonctions retournant une valeur, pour obtenir par exemple la date
courante ou la prochaine valeur d’une séquence. Les valeurs fournies par
la clause VALUES
ou par la requête sont associées à la
liste explicite ou implicite des colonnes de gauche à droite.
Exemples
Insertion d’une ligne dans la table stock
:
INSERT INTO stock (vin_id, contenant_id, annee, nombre)
VALUES (12, 1, 1935, 1);
Insertion d’une ligne dans la table vin
:
INSERT INTO vin (id, recoltant_id, appellation_id, type_vin_id)
VALUES (nextval('vin_id_seq'), 3, 6, 1);
L’ordre INSERT
peut aussi prendre une requête SQL en
entrée. Dans ce cas, INSERT
va insérer autant de lignes
dans la table d’arrivée qu’il y a de lignes retournées par la requête
SELECT
. L’ordre des colonnes retournées par
SELECT
doit correspondre à l’ordre des colonnes de la liste
des colonnes. Leur type de données doit également correspondre.
Exemples
Insertion dans une table stock2
à partir d’une requête
SELECT sur la table stock1
:
INSERT INTO stock2 (vin_id, contenant_id, annee, nombre)
SELECT vin_id, contenant_id, annee, nombre FROM stock;
Il est préférable de lister explicitement les colonnes touchées par
l’ordre INSERT
afin de garder un ordre d’insertion
déterministe. En effet, l’ordre des colonnes peut changer notamment
lorsque certains ETL sont utilisés pour modifier le type d’une colonne
varchar(10)
en varchar(11)
. Par exemple, pour
la colonne username
, l’ETL Kettle génère les ordres
suivants :
ALTER TABLE utilisateurs ADD COLUMN username_KTL VARCHAR(11);
UPDATE utilisateurs SET username_KTL=username;
ALTER TABLE utilisateurs DROP COLUMN username;
ALTER TABLE utilisateurs RENAME username_KTL TO username
Il génère des ordres SQL inutiles et consommateurs d’entrées/sorties disques car il doit générer des ordres SQL compris par tous les SGBD du marché. Or, tous les SGBD ne permettent pas de changer le type d’une colonne aussi simplement que dans PostgreSQL.
Exemples
Exemple de modification du schéma pouvant entrainer des problèmes d’insertion si les colonnes ne sont pas listées explicitement :
CREATE TABLE insere (id integer PRIMARY KEY, col1 varchar(5), col2 integer);
INSERT INTO insere VALUES (1, 'XX', 10);
ALTER TABLE insere ADD COLUMN col1_tmp varchar(6);
UPDATE insere SET col1_tmp = col1;
ALTER TABLE insere DROP COLUMN col1;
ALTER TABLE insere RENAME COLUMN col1_tmp TO col1;
INSERT INTO insere VALUES (2, 'XXX', 10);
for integer: "XXX"
ERROR: invalid input syntax 1: INSERT INTO insere VALUES (2, 'XXX', 10); LINE
L’ordre de mise à jour de lignes s’appelle UPDATE
.
L’ordre UPDATE
permet de mettre à jour les lignes d’une
table.
L’ordre UPDATE
ne met à jour que les lignes qui
satisfont les conditions de la clause WHERE
. La clause
SET
permet de définir les colonnes à mettre à jour. Le nom
des colonne mises à jour doivent faire partie de la table mise à
jour.
Les valeurs mises à jour peuvent faire référence aux valeurs avant
mise à jour de la colonne, dans ce cas on utilise la forme
nom_colonne = nom_colonne
. La partie de gauche référence la
colonne à mettre à jour, la partie de droite est une expression qui
permet de déterminer la valeur à appliquer à la colonne. La valeur à
appliquer peut bien entendu être une référence à une ou plusieurs
colonnes et elles peuvent être dérivées par une opération
arithmétique.
La clause FROM
ne fait pas partie de la norme SQL mais
certains SGBDR la supportent, notamment SQL Server et PostgreSQL. Elle
permet de réaliser facilement la mise à jour d’une table à partir des
valeurs d’une ou plusieurs tables annexes.
La norme SQL permet néanmoins de réaliser des mises à jour en
utilisant une sous-requête, permettant d’éviter l’usage de la clause
FROM
.
Exemples
Mise à jour du prix d’un livre particulier :
UPDATE livres SET prix = 10 WHERE isbn = '978-3-8365-3872-5';
Augmentation de 5 % du prix des livres :
UPDATE livres SET prix = prix * 1.05;
Mise à jour d’une table employees
à partir des données
d’une table bonus_plan
:
UPDATE employees e
SET commission_rate = bp.commission_rate
FROM bonus_plan bp
ON (e.bonus_plan = bp.planid)
La même requête avec une sous-requête, conforme à la norme SQL :
UPDATE employees
SET commission_rate = (SELECT commission_rate
FROM bonus_plan bp
WHERE bp.planid = employees.bonus_plan);
Lorsque plusieurs colonnes doivent être mises à jour à partir d’une jointure, il est possible d’utiliser ces deux écritures :
UPDATE employees e
SET commission_rate = bp.commission_rate,
= bp.commission_rate2
commission_rate2 FROM bonus_plan bp
ON (e.bonus_plan = bp.planid);
et
UPDATE employees e
SET (commission_rate, commission_rate2) = (
SELECT bp.commission_rate, bp.commission_rate2
FROM bonus_plan bp ON (e.bonus_plan = bp.planid)
);
L’ordre DELETE
supprime l’ensemble des lignes qui
répondent au prédicat de la clause WHERE
.
DELETE FROM nom_table [ [ AS ] alias ]
WHERE condition | WHERE CURRENT OF nom_curseur ] [
Exemples
Suppression d’un livre épuisé du catalogue :
DELETE FROM livres WHERE isbn = '978-0-8707-0635-6';
La clause RETURNING
est une extension de PostgreSQL.
Elle permet de retourner les lignes insérées, mises à jour ou supprimées
par un ordre DML de modification. Il est également possible de dériver
une valeur retournée.
L’emploi de la clause RETURNING
peut nécessiter des
droits complémentaires sur les objets de la base.
Exemples
Mise à jour du nombre de bouteilles en stock :
SELECT annee, nombre FROM stock
WHERE vin_id = 7 AND contenant_id = 1 AND annee = 1967;
annee | nombre-------+--------
1967 | 17
1 row)
(
UPDATE stock SET nombre = nombre - 1
WHERE vin_id = 7 AND contenant_id = 1 AND annee = 1967 RETURNING nombre;
nombre--------
16
1 row) (
Les transactions sont une partie essentielle du langage SQL. Elles permettent de rendre atomique un certain nombre de requêtes. Le résultat de toutes les requêtes d’une transaction est validée ou pas, mais on ne peut pas avoir d’état intermédiaire.
Le langage SQL définit qu’une transaction peut être validée ou
annulée. Ce sont respectivement les ordres COMMIT
et
ROLLBACK
. Il est aussi possible de faire des points de
reprise ou de sauvegarde dans une transaction. Ils se font en utilisant
l’ordre SAVEPOINT
.
PostgreSQL fonctionne en auto-commit. Autrement dit, sans
BEGIN
, une requête est considérée comme une transaction
complète et n’a donc pas besoin de COMMIT
.
Une transaction débute toujours par un START
ou un
BEGIN
.
Une transaction est toujours terminée par une COMMIT
ou
un END
quand on veut que les modifications soient
définitivement enregistrées, et par un ROLLBACK
dans le cas
contraire.
La transaction en cours d’une session qui se termine, quelle que soit
la raison, sans COMMIT
et sans ROLLBACK
est
considérée comme annulée.
Exemples
Avant de retirer une bouteille du stock, on vérifie tout d’abord qu’il reste suffisamment de bouteilles en stock :
BEGIN TRANSACTION;
SELECT annee, nombre FROM stock WHERE vin_id = 7 AND contenant_id = 1
AND annee = 1967;
annee | nombre-------+--------
1967 | 17
1 row)
(
UPDATE stock SET nombre = nombre - 1
WHERE vin_id = 7 AND contenant_id = 1 AND annee = 1967 RETURNING nombre;
nombre--------
16
1 row)
(
COMMIT;
La plupart des langages permettent de gérer les transactions à l’aide de méthodes ou fonctions particulières. Il est recommandé de les utiliser.
En Java, ouvrir une transaction revient à désactiver l’auto-commit :
String url =
"jdbc:postgresql://localhost/test?user=fred&password=secret&ssl=true";
Connection conn = DriverManager.getConnection(url);
.setAutoCommit(false); conn
La transaction est confirmée (COMMIT
) avec la méthode
suivante :
.commit(); conn
À l’inverse, elle est annulée (ROLLBACK
) avec la méthode
suivante :
.rollback(); conn
déroule jusqu’au bout, le point de sauvegarde pourra être relâché
(RELEASE SAVEPOINT
), confirmant ainsi les traitements. Si
le traitement tombe en erreur, il suffira de revenir au point de
sauvegarde (ROLLBACK TO SAVEPOINT
pour annuler uniquement
cette partie du traitement sans affecter le reste de la transaction.
Les points de sauvegarde sont des éléments nommés, il convient donc de leur affecter un nom particulier. Leur nom doit être unique dans la transaction courante.
Les langages de programmation permettent également de gérer les points de sauvegarde en utilisant des méthodes dédiées. Par exemple, en Java :
Savepoint save1 = connection.setSavepoint();
En cas d’erreurs, la transaction peut être ramener à l’état du point de sauvegarde avec :
.rollback(save1); connection
À l’inverse, un point de sauvegarde est relâché de la façon suivante :
.releaseSavepoint(save1); connection
Exemples
Transaction avec un point de sauvegarde et la gestion de l’erreur :
BEGIN;
INSERT INTO mere (id, val_mere) VALUES (10, 'essai');
SAVEPOINT insert_fille;
INSERT INTO fille (id_fille, id_mere, val_fille) VALUES (1, 10, 'essai 2');
key value violates unique constraint "fille_pkey"
ERROR: duplicate Key (id_fille)=(1) already exists.
DETAIL:
ROLLBACK TO SAVEPOINT insert_fille;
COMMIT;
SELECT * FROM mere;
id | val_mere
----+----------
1 | mere 1
2 | mere 2
10 | essai
Le standard SQL permet de traiter des ensembles d’enregistrements,
que ce soit en lecture, en insertion, en modification et en suppression.
Les ensembles d’enregistrements sont généralement des tables qui, comme
tous les autres objets, sont créées (CREATE
), modifier
(ALTER
) et/ou supprimer (DROP
).
Cet exercice utilise la base tpc.
La base tpc (dump de 31 Mo, pour 267 Mo sur le disque au final) et ses utilisateurs peuvent être installés comme suit :
curl -kL https://dali.bo/tp_tpc -o /tmp/tpc.dump
curl -kL https://dali.bo/tp_tpc_roles -o /tmp/tpc_roles.sql
# Exécuter le script de création des rôles
psql < /tmp/tpc_roles.sql
# Création de la base
createdb --owner tpc_owner tpc
# L'erreur sur un schéma 'public' existant est normale
pg_restore -d tpc /tmp/tpc.dump
rm -- /tmp/tpc.dump /tmp/tpc_roles.sql
Les mots de passe sont dans le script
/tmp/tpc_roles.sql
. Pour vous connecter :
$ psql -U tpc_admin -h localhost -d tpc
Pour cet exercice, les modifications de schéma doivent être effectuées par un rôle ayant suffisamment de droits pour modifier son schéma. Le rôle tpc_admin a les droits suffisants.
Ajouter une colonne
text
à la tablecontacts
. Cette colonne va permettre de stocker l’adresse e-mail des clients et des fournisseurs. Ajouter également un commentaire décrivant cette colonne dans le catalogue de PostgreSQL (utiliser la commandeCOMMENT
).
Mettre à jour la table des contacts pour indiquer l’adresse e-mail de Client6657 qui est
client6657@dalibo.com
.
Ajouter une contrainte d’intégrité qui valide que la valeur de la colonne
@
).
Valider la contrainte dans une transaction de test.
Déterminer quels sont les contacts qui disposent d’une adresse e-mail et affichez leur nom ainsi que le code de leur pays.
La génération des numéros de commande est actuellement réalisée à l’aide de la séquence
commandes_commande_id_seq
. Cette méthode ne permet pas de garantir que tous les numéros de commande se suivent. Proposer une solution pour sérialiser la génération des numéros de commande. Autrement dit, proposer une méthode pour obtenir un numéro de commande sans avoir de « trou » dans la séquence en cas d’échec d’une transaction.
Noter le nombre de lignes de la table
pieces
. Dans une transaction, majorer de 5% le prix des pièces de moins de 1500 € et minorer de 5 % le prix des pièces dont le prix actuel est égal ou supérieur à 1500 €. Vérifier que le nombre de lignes mises à jour au total correspond au nombre total de lignes de la tablepieces
.
Dans une même transaction, créer un nouveau client en incluant l’ajout de l’ensemble des informations requises pour pouvoir le contacter. Un nouveau client a un solde égal à 0.
Ajouter une colonne
text
à la tablecontacts
. Cette colonne va permettre de stocker l’adresse e-mail des clients et des fournisseurs. Ajouter également un commentaire décrivant cette colonne dans le catalogue de PostgreSQL (utiliser la commandeCOMMENT
).
-- Ajouter une colonne email de type text
ALTER TABLE contacts
ADD COLUMN email text;
-- Ajouter un commentaire
COMMENT ON COLUMN contacts.email IS 'Adresse e-mail du contact';
Mettre à jour la table des contacts pour indiquer l’adresse e-mail de Client6657 qui est
client6657@dalibo.com
.
UPDATE contacts
SET email = 'client6657@dalibo.com'
WHERE nom = 'Client6657';
Vérifier les résultats :
SELECT *
FROM contacts
WHERE nom = 'Client6657';
Ajouter une contrainte d’intégrité qui valide que la valeur de la colonne
@
).
ALTER TABLE contacts
ADD CONSTRAINT chk_contacts_email_valid
CHECK (email LIKE '%@%');
Cette expression régulière est simplifiée et simpliste pour les besoins de l’exercice. Des expressions régulières plus complexes permettent de valider réellement une adresse e-mail.
Voici un exemple un tout petit peu plus évolué en utilisant une
expression rationnelle simple, ici pour vérifier que la chaîne précédent
le caractère @
contient au moins un caractère, et que la
chaîne le suivant est une chaîne de caractères contenant un point :
ALTER TABLE contacts
ADD CONSTRAINT chk_contacts_email_valid
CHECK (email ~ '.+@.+\..+');
Valider la contrainte dans une transaction de test.
Démarrer la transaction :
BEGIN ;
Tenter de mettre à jour la table contacts
avec une
adresse e-mail ne répondant pas à la contrainte :
UPDATE contacts
SET email = 'test';
L’ordre UPDATE
retourne l’erreur suivante, indiquant que
l’expression régulière est fonctionnelle :
ERROR: new row for relation "contacts" violates check constraint
"chk_contacts_email_valid"
DETAIL: Failing row contains
(300001, Client1737, nkD, SA, 20-999-929-1440, test).
La transaction est ensuite annulée :
ROLLBACK ;
Déterminer quels sont les contacts qui disposent d’une adresse e-mail et affichez leur nom ainsi que le code de leur pays.
SELECT nom, code_pays
FROM contacts
WHERE email IS NOT NULL;
La génération des numéros de commande est actuellement réalisée à l’aide de la séquence
commandes_commande_id_seq
. Cette méthode ne permet pas de garantir que tous les numéros de commande se suivent. Proposer une solution pour sérialiser la génération des numéros de commande. Autrement dit, proposer une méthode pour obtenir un numéro de commande sans avoir de « trou » dans la séquence en cas d’échec d’une transaction.
La solution la plus simple pour imposer la sérialisation des numéros de commandes est d’utiliser une table de séquences. Une ligne de cette table correspondra au compteur des numéros de commande.
-- création de la table qui va contenir la séquence :
CREATE TABLE numeros_sequences (
NOT NULL PRIMARY KEY,
nom text sequence integer NOT NULL
);
-- initialisation de la séquence :
INSERT INTO numeros_sequences (nom, sequence)
SELECT 'sequence_numero_commande', max(numero_commande)
FROM commandes;
L’obtention d’un nouveau numéro de commande sera réalisé dans la transaction de création de la commande de la façon suivante :
BEGIN ;
UPDATE numeros_sequences
SET sequence = sequence + 1
WHERE nom = 'numero_commande'
RETURNING sequence;
/* insertion d'une nouvelle commande en utilisant le numéro de commande
retourné par la commande précédente :
INSERT INTO commandes (numero_commande, ...)
VALUES (<la nouvelle valeur de la séquence>, ...) ;
*/
COMMIT ;
L’ordre UPDATE
pose un verrou exclusif sur la ligne mise
à jour. Tant que la mise à jour n’aura pas été validée ou annulée par
COMMIT
ou ROLLBACK
, le verrou posé va bloquer
toutes les autres transactions qui tenteraient de mettre à jour cette
ligne. De cette façon, toutes les transactions seront sérialisées.
Concernant la génération des numéros de séquence, si la transaction
est annulée, alors le compteur sequence
retrouvera sa
valeur précédente et la transaction suivante obtiendra le même numéro de
séquence. Si la transaction est validée, alors le compteur
sequence
est incrémenté. La transaction suivante verra
alors cette nouvelle valeur et non plus l’ancienne. Cette méthode
garantit qu’il n’y ait pas de rupture de séquence.
Il va de soi que les transactions de création de commandes doivent être extrêmement courtes. Si une telle transaction est bloquée, toutes les transactions suivantes seront également bloquées, paralysant ainsi tous les utilisateurs de l’application.
Noter le nombre de lignes de la table
pieces
. Dans une transaction, majorer de 5% le prix des pièces de moins de 1500 € et minorer de 5 % le prix des pièces dont le prix actuel est égal ou supérieur à 1500 €. Vérifier que le nombre de lignes mises à jour au total correspond au nombre total de lignes de la tablepieces
.
BEGIN ;
SELECT count(*)
FROM pieces;
UPDATE pieces
SET prix = prix * 1.05
WHERE prix < 1500;
UPDATE pieces
SET prix = prix * 0.95
WHERE prix >= 1500;
Au total, la transaction a mis à jour 214200 (99922+114278) lignes, soit 14200 lignes de trop mises à jour.
Annuler la mise à jour :
ROLLBACK ;
Explication : Le premier UPDATE
a majoré de 5 % les
pièces dont le prix est inférieur à 1500 €. Or, tous les prix supérieurs
à 1428,58 € passent la barre des 1500 € après le premier
UPDATE
. Le second UPDATE
minore les pièces
dont le prix est égal ou supérieur à 1500 €, ce qui inclue une partie
des prix majorés par le précédent UPDATE
. Certaines lignes
ont donc subies deux modifications au lieu d’une. L’instruction
CASE
du langage SQL, qui sera abordée dans le prochain
module, propose une solution à ce genre de problématique :
UPDATE pieces
SET prix = (
CASE
WHEN prix < 1500 THEN prix * 1.05
WHEN prix >= 1500 THEN prix * 0.95
END
);
Dans une même transaction, créer un nouveau client en incluant l’ajout de l’ensemble des informations requises pour pouvoir le contacter. Un nouveau client a un solde égal à 0.
-- démarrer la transaction
BEGIN ;
-- créer le contact et récupérer le contact_id généré
INSERT INTO contacts (nom, adresse, telephone, code_pays)
VALUES ('M. Xyz', '3, Rue du Champignon, 96000 Champiville',
'+33554325432', 'FR')
RETURNING contact_id;
-- réaliser l'insertion en utilisant le numéro de contact récupéré précédemment
INSERT INTO clients (solde, segment_marche, contact_id, commentaire)
-- par exemple ici avec le numéro 350002
VALUES (0, 'AUTOMOBILE', 350002, 'Client très important');
-- valider la transaction
COMMIT ;