Tables et contraintes

Schémas de tables

La structure d'une table, appelée schémas, est constituée du nom et du type de chaque colonne :

{"tablename": "'Users'"}

{"tablename": "'Produits'"}

est l'identifiant de la colonne.
est le nom de la colonne.
est le type de données stockées dans la colonne.
indique si la colonne peut contenir des valeurs .
est la valeur par défaut de la colonne (i.e. valeur insérée si non spécifiée lors de l'insertion).
indique si la colonne fait partie d'une clé primaire (cf suite du cours).
indique si la colonne est générée (cf suite du cours)

⚠ La manière d'afficher le schéma d'une table diffère d'un SGBD à l'autre :

SGBD	Commande
SQLite
PostgreSQL
MySQL

Opérations sur les tables

Jusqu'à présent, nous avons écrit des requêtes SQL pour récupérer, insérer, modifier, et supprimer des entrées d'une table. De manière analogue est aussi possible de créer, modifier, et supprimer des tables via des commandes SQL :

: créer une table ;
: modifier une table ;
: supprimer toutes les entrées d'une table ;
: supprimer une table.

Création d'une table

La requête SQL permet de créer une nouvelle table, et se présente usuellement sous la forme suivante :

est le nom de la colonne ;
est le type des données stockées dans la colonne.

⚠ Si vous utilisez SQLite, il faut ajouter à la fin de la requête SQL afin de le forcer à vérifier les types des colonnes lors des opérations sur la table.

{"m_cols": "(Ref TEXT, Q INT)", "m_vals": "('Crayon', 4)"}

{"m_cols": "(Ref TEXT, Q INT)", "m_vals": "(4, 'Crayon')"}

IF NOT EXISTS

Si la table existe déjà, une erreur sera lancée. Pour éviter cela, vous pouvez ajouter la clause qui créera la table seulement si elle n'existe pas.

⚠ Après un , la table ne sera pas nécessairement vide. Si vous avez besoin que la table soit vide, utilisez une requête (cf suite du cours).

⚠ Si le schéma de table du est différent de la table existante, aucune erreur ne sera lancée.

{"ifexists": ""}

{"ifexists": "IF NOT EXISTS"}

CREATE TABLE AS

💡 Vous pouvez aussi créer et remplir une nouvelle table à partir d'entrées grâce à la clause .

⚠ Cette méthode ne permet pas de préciser les contraintes de la table ainsi créez. Ainsi, il est préférable de d'abord créer une table vide (avec ses contraintes) avec , puis de la remplir avec .

{"cols": "*", "tablename": "Produits", "cond": "1==1"}

{"cols": "Date, Q", "tablename": "Produits", "cond": "Ref == 'Gomme'"}

⚠ Sur SQLite, les tables créées de cette manière ne sont pas .

Exemples

💡 Pour références, les requêtes SQL utilisées pour créer les tables des exemples peuvent être visualisées ci-dessous :

{"tablename": "'Produits'"}

{"tablename": "'Users'"}

Suppression d'une table

Les requêtes SQL et permettent, respectivement, de supprimer et de vider une table existantes, et se présentent usuellement sous la forme suivante :

{"command": "DROP TABLE"}

{"command": "DELETE FROM"}

💡 Dans certains SGBD, supporte une clause . Ainsi, si vous souhaitez créer une table vide, vous pouvez la avant sa création pour vous assurer de la construire correctement.

⚠ n'existe pas sur SQLite. Il vous faut alors utiliser à la place.

Modification d'une table

La requête SQL permet de modifier une table, et se présente usuellement sous la forme suivante :

renomme une colonne ( = ).
ajoute une colonne ( = ).
supprime une colonne ( = ).

{"command": "RENAME", "colname": "Ref", "opts": "TO Prod"}

{"command": "ADD", "colname": "Sum", "opts": "INT"}

{"command": "DROP", "colname": "Date", "opts": ""}

⚠ Renommer ou supprimer la colonne d'une table n'est pas anodin. En effet, si ces colonnes sont utilisées ailleurs, e.g. dans des requêtes SQL, ces dernières ne fonctionneront plus correctement.

💡 Certains SGBD supportent des clauses et sur les colonnes.

💡 Vous pouvez aussi renommer une table avec la commande SQL suivante :

Les contraintes

Afin de garantir la cohérence et consistence des tables, il est possible de définir des contraintes sur les colonnes, ainsi que sur les tables. Par exemple, il est possible de définir des contraintes sur des colonnes "numéro de téléphone", "adresse e-mail", etc. pour s'assurer que les données stockées suivent bien le format d'un numéro de téléphone ou d'une adresse mail.

💡 L'assurance de la structure des données stockées et manipulées constitue un des avantages majeurs des bases de données SQL.

Contraintes sur les colonnes

Lors de la création de la table, il est possible de spécifier des contraintes sur des colonnes. Pour ce faire, on ajoute le(s) contrainte(s) après le type de la colonne :

{"cstrnt": "DEFAULT 'D'", "vals": "(1)", "cols": "(A)"}

{"cstrnt": "NOT NULL", "vals": "(1)", "cols": "(A)"}

{"cstrnt": "UNIQUE", "vals": "(1, 2), (1, 2)"}

{"cstrnt": "CHECK(B == UPPER(B) )", "vals": "(1, 'Nom')"}

: valeur par défaut si non renseignée lors d'un .
: la valeur ne peut pas être .
: il ne peut pas y avoir deux valeurs identiques dans la colonne.
⚠ La colonne peut contenir plusieurs valeurs .
: la valeur de la colonne doit respecter la condition .

Contraintes sur la table

Les contraintes et peuvent dépendre de plusieurs colonnes. Dans ce cas, la contrainte est ajoutée après la liste des colonnes :

{"cstrnt": "UNIQUE(A, B)", "vals": "(1, 2), (1, 2)" }

{"cstrnt": "CHECK(B != A)", "vals": "(1, 1)"}

Colonnes générées

Il est possible de générer la valeur d'une colonne à partir des valeurs d'autres colonnes grâce à la contrainte :

{"cstrnt": "(PU*Q)", "vals": "(1,2), (3,4)" }

{"cstrnt": "(PU*Q) STORED", "vals": "(1,2), (3,4)"}

💡 signifie que la valeur sera calculée lors des insertions/modifications puis stockées. Si absent, la valeur sera recalculée à chaque accès.

Les index

Il est fréquent de vouloir récupérer une entrée à partir d'un identifiant donné. Cependant, s'il faut à chaque fois parcourir toutes les entrées de la table unes à unes, cela peut s'avérer relativement lent sur de grandes tables.

Un index est une structure permettant de trouver très rapidement la/les entrée(s) associée(s) à un tel identifiant.

💡 Lorsqu'une colonne a la contrainte , le SGBD créé automatiquement un index.

Les clefs primaires (PK)

Une clef primaire est un index un peu spécial accélérant encore plus les recherches d'entrées à partir d'un identifiant. Il est un champ (ou un ensemble de champs) identifiant de manière unique une entrée.

Elle est créée via une contrainte et implique les contraintes . Il ne peut y avoir qu'une clef primaire par table.

{"pk": "TEXT PRIMARY KEY", "vals": "('1'), ('2')"}

{"pk": "TEXT PRIMARY KEY", "vals": "('1'), ('1')"}

{"pk": "INTEGER PRIMARY KEY\n AUTOINCREMENT", "vals": "(NULL), (NULL)"}

💡 La contrainte permet d'automatiquement incrémenter la clef primaire des entrées insérées. Elle ne peut être utilisée que sur une colonne .

💡 Comme avec la contrainte , il est aussi possible de créer une clef primaire sur plusieurs colonnes via contrainte de table :

{"pk": "PRIMARY KEY(ID, CODE)", "vals": "(1, 'E')"}

💡 Certains SGBD créent automatiquement, pour chaque table, une clef primaire cachée nommée .

Les clefs étrangères (FK)

Il est fréquent, dans une base de données, que des entrées fassent référence à des entrées d'une autre table. Par exemple, une table pourra mettre en relation une table et une table .

💡 Le terme de "base de données relationnelles" vient en partie de là : on établie des relations entre différentes entrées/données. Ceci constitue une des fonctionnalités majeurs des bases de données SQL.

Pour cela, on utilise une clef étrangère (FK) afin de garantir l'existence de la donnée référencée. Elle est composée d'une ou plusieurs colonnes référençant une ou plusieurs colonnes de contrainte ou . On l'indique soit via une contrainte , ou via une contrainte de table :

est la table référencée.
sont les colonnes constituant la clé étrangère.
sont les colonnes référencées.

💡 Si la/les colonne(s) de la table référencée ne sont pas spécifiées, la/les colonne(s) de la clef primaire sont utilisée(s).

{"fk": "REFERENCES Users", "vals": "(1, 'E')"}

{"fk": "REFERENCES Users", "vals": "(4, 'E')"}

{"fk": "REFERENCES Users", "vals": "(NULL, 'E')"}

💡 La clé étrangère peut être nulle (sauf si contrainte ).

💡 On préfère souvent nommer la/les colonne(s) de la clef étrangères avec les noms de la/les colonne(s) référencée(s).

⚠ Les clefs étrangères ne sont qu'une contrainte garantissant l'existence de la valeur référencée. Elle ne sont pas une optimisation en soit (mais peuvent aider le SGBD à optimiser la requête).

⚠ La vérification des clefs étrangères n'est pas activé par défaut sur SQLite. La commande permet de l'activer.

UML

Les bases de données ont généralement de très nombreuses tables. Il est alors difficile de se faire une idée de sa structure, du schéma des tables et de leurs relations, sans une représentation appropriée. Pour cela on utilise soit un diagramme UML, soit un diagramme Merise. Le dernier étant très franco-français, nous nous concentrerons sur le premier.

Dans un diagramme UML, chaque table est représentée par un rectangle découpé en 3 cadres contenant :

le nom de la table ;
les colonnes de la table ;
les contraintes de la table.

Par convention, les noms de colonnes sont formattés comme suit :

en gras pour les clefs primaires ;
en italique pour les clefs étrangères ;
soulignés pour les colonnes uniques.

Une flèche est aussi tirée des clefs étrangères jusqu'aux clefs primaires qu'elles référencent.

💡 En fonction des besoins, il est possible d'ajouter plus ou moins de détails, comme e.g. les types et contraintes de colonnes.

{"pk": "ID", "fk": "ID", "cols_a": "ID INT, CODE TEXT", "cols_b": "ID INT, CODE TEXT"}

{"pk": "ID, CODE", "fk": "ID, CODE", "cols_a": "CODE TEXT, X INT, ID INT", "cols_b": "ID INT, CODE TEXT"}

Opérations sur table avec contraintes

Modification/Suppression sur des colonnes référencées

Comme nous l'avons vu à la section précédente, une clef étrangère référence des colonnes d'une autre table. Mais que se passe-t-il lorsqu'on modifie ou supprime des entrées dans les colonnes référencées ?

C'est à vous de le définir via les clauses et sur la clef étrangère :

{"pol": "RESTRICT"}

{"pol": "CASCADE"}

{"pol": "SET NULL"}

: empêche la modification/suppression si les valeurs sont référencées par une clef étrangère.
: modifie/supprime la clef étrangère lorsque des valeurs des colonnes référencées sont modifiées.
: modifie la clef étrangère lorsque des valeurs des colonnes référencées sont modifiées.

Merge : mettre à jour si existe, sinon insérer (en TD ?).

En gros la problématique est d'ajouter si inexistant, et de modifier si existant.

On peut faire un select, puis refaire une requête pour update ou insert, mais c'est pas pratique de faire plusieurs requêtes.

On a des choses comme REPLACE (MySQL) supprime l'entrée si elle existe, puis ajoute la nouvelle. Sauf que s'il y a des clefs primaires référencées par des clefs étrangères ça casse tout.

Il faut ainsi faire cela en une seule opération. Pour cela on a UPSERT et INSERT ON DUPLICATE KEYS UPDATE (dépend du SGBD) qui s'utilise comme INSERT et qui sont plutôt simples (vérifient si la clef primaire existe).

Et pour d'autres, MERGE (SQL2003), qui est plus puissant (on peut personnaliser la condition), et l'utiliser pour d'autres usages (on peut aussi supprimer).

évoquer https://sql.sh/cours/merge (à la fin d'un TP ?)

=> ajouter (ou modifier si existe) => besoin notion de clef primaire.

UPSERT.
ajouter ou modifier si existe (seulement mysql) https://dev.mysql.com/doc/refman/8.4/en/insert-on-duplicate.html https://dev.mysql.com/doc/refman/9.0/en/replace.html

Replace à éviter ( https://stackoverflow.com/questions/9168928/what-are-practical-differences-between-replace-and-insert-on-duplicate-key ).

Modifier les contraintes

Pour information, certains SGBD (pas sur SQLite) permettent de modifier les contraintes de colonnes et de tables :

⚠ Modifier le schéma et les contraintes d'une table existante/non-vide n'est pas anodin.