Types de données et contraintes

Contenu

Types de données et contraintes#

Pourquoi les types et les contraintes importent#

Un système de types bien conçu est la première ligne de défense de l’intégrité d’une base de données. En forçant chaque colonne à n’accepter que des valeurs conformes à son type et à ses contraintes, le SGBDR garantit que les données seront cohérentes avant même qu’une ligne de code applicatif ne soit exécutée. Un salaire négatif, une adresse email à NULL obligatoire, une clé étrangère pointant vers un enregistrement inexistant : ces erreurs sont interceptées au niveau de la base, indépendamment du code applicatif.

Les types SQL#

Types entiers#

Définition 27 (Types entiers)

Les types entiers stockent des nombres entiers sans partie décimale.

INTEGER (ou INT) : entier signé, typiquement 4 octets (−2 147 483 648 à 2 147 483 647).
BIGINT : entier 8 octets pour de très grands identifiants ou compteurs.
SMALLINT : entier 2 octets (-32 768 à 32 767), économique en espace.
TINYINT : entier 1 octet (0 à 255 ou −128 à 127 selon le SGBDR).

SQLite est particulier : il ne possède qu’un seul type interne entier à 8 octets, mais accepte tous ces mots-clés pour des raisons de compatibilité.

Types numériques à virgule#

Définition 28 (Types à virgule flottante et décimaux)

REAL / FLOAT / DOUBLE PRECISION : nombres en virgule flottante IEEE 754. Rapides mais sujets aux erreurs d’arrondi. À éviter pour les montants financiers.
NUMERIC(p, s) / DECIMAL(p, s) : nombre décimal exact avec \(p\) chiffres significatifs et \(s\) chiffres après la virgule. Indispensable pour les calculs financiers.

Exemple : DECIMAL(10, 2) stocke jusqu’à 99999999.99.

Remarque 10

Ne jamais utiliser FLOAT pour les montants monétaires : 0.1 + 0.2 en virgule flottante IEEE 754 donne 0.30000000000000004. Utiliser NUMERIC ou DECIMAL qui calcule en base 10 exacte.

Types texte#

Définition 29 (Types texte)

TEXT : chaîne de longueur variable sans limite imposée par le standard SQL (pratique de la plupart des SGBDR).
VARCHAR(n) : chaîne de longueur variable avec un maximum de \(n\) caractères.
CHAR(n) : chaîne de longueur fixe \(n\), complétée par des espaces si nécessaire.
CLOB : Character Large Object, pour de très longs textes.

SQLite utilise le type affinity TEXT pour tout ce qui ressemble à du texte. PostgreSQL recommande TEXT plutôt que VARCHAR sans limite. MySQL distingue rigoureusement CHAR et VARCHAR.

Types binaires et autres#

Définition 30 (Types BLOB, BOOLEAN, DATE et DATETIME)

BLOB (Binary Large Object) : données binaires brutes (images, fichiers, etc.).
BOOLEAN : valeur booléenne. SQLite n’a pas de type booléen natif — il stocke 0 (faux) et 1 (vrai) comme des entiers. PostgreSQL dispose d’un vrai type BOOLEAN.
DATE : date (année-mois-jour), format ISO 8601 : '2024-03-15'.
DATETIME / TIMESTAMP : date et heure. SQLite stocke les dates comme du texte ISO 8601, des nombres réels (jours juliens) ou des entiers (epoch Unix).
JSON : type natif dans PostgreSQL (avec jsonb pour le binaire indexable) et MySQL 5.7+. SQLite ne l’a pas nativement mais peut stocker du JSON en TEXT.

Différences SQLite / PostgreSQL / MySQL#

_images/60d30b6fecfa5399c46f70ac9cf57a81e0ef4a8d2a595d5f6ec8caf424b1c3fc.png

Contraintes#

Les contraintes sont des règles d’intégrité déclarées dans le schéma, vérifiées automatiquement par le SGBDR à chaque insertion ou modification.

NOT NULL#

Définition 31 (Contrainte NOT NULL)

NOT NULL interdit qu’une colonne contienne la valeur NULL. Sans cette contrainte, toute colonne accepte NULL par défaut (valeur inconnue ou absente).

Une colonne NOT NULL doit recevoir une valeur explicite à l’insertion (ou avoir une valeur DEFAULT).

UNIQUE#

Définition 32 (Contrainte UNIQUE)

UNIQUE garantit que toutes les valeurs d’une colonne (ou d’un groupe de colonnes) sont distinctes dans la table. Contrairement à PRIMARY KEY, une colonne UNIQUE peut contenir NULL — et en général plusieurs NULL sont acceptés (car NULL ≠ NULL en logique ternaire).

PRIMARY KEY#

Définition 33 (Contrainte PRIMARY KEY)

PRIMARY KEY combine NOT NULL et UNIQUE. Elle identifie de manière unique chaque ligne. Une table ne peut avoir qu’une seule clé primaire, qui peut être mono-attribut ou composite (définie au niveau de la table).

Dans SQLite, une colonne INTEGER PRIMARY KEY devient automatiquement un alias pour le rowid interne — elle est auto-incrémentée si aucune valeur n’est fournie.

FOREIGN KEY#

Définition 34 (Contrainte FOREIGN KEY)

FOREIGN KEY (col) REFERENCES table_parente(col_pk) impose que toute valeur non-NULL de col corresponde à une valeur existante de col_pk dans table_parente.

Les actions référentielles définissent le comportement en cas de modification de la clé parente :

ON DELETE CASCADE : supprime les lignes enfants automatiquement.
ON DELETE SET NULL : met la clé étrangère à NULL.
ON DELETE RESTRICT : refuse la suppression si des enfants existent.
ON DELETE NO ACTION : comportement identique à RESTRICT en SQLite.

Remarque 11

Dans SQLite, les clés étrangères sont désactivées par défaut pour des raisons de compatibilité ascendante. Il faut les activer explicitement avec PRAGMA foreign_keys = ON; à chaque connexion.

CHECK#

Définition 35 (Contrainte CHECK)

CHECK(expression) évalue une expression booléenne à chaque insertion ou modification. Si l’expression est fausse, l’opération est rejetée. L’expression peut référencer une ou plusieurs colonnes de la même ligne.

Exemples : CHECK(salaire >= 0), CHECK(date_fin >= date_debut), CHECK(statut IN ('actif', 'inactif', 'archive')).

DEFAULT#

Définition 36 (Contrainte DEFAULT)

DEFAULT valeur fournit une valeur par défaut lorsque la colonne n’est pas mentionnée dans une instruction INSERT. La valeur peut être une constante, une expression ou une fonction (CURRENT_TIMESTAMP, CURRENT_DATE).

Contraintes de colonne vs contraintes de table#

Définition 37 (Niveaux de déclaration des contraintes)

Une contrainte de colonne est déclarée directement après la définition du type d’une colonne. Elle ne peut référencer que cette seule colonne.
Une contrainte de table est déclarée à la fin de la liste des colonnes, après une virgule. Elle peut référencer plusieurs colonnes (ex. : clé primaire composite, contrainte UNIQUE multi-colonnes, clé étrangère composite).

Un nom peut être donné à une contrainte avec CONSTRAINT nom_contrainte pour faciliter le débogage des messages d’erreur.

conn = sqlite3.connect(":memory:")
conn.execute("PRAGMA foreign_keys = ON")

conn.executescript("""
-- Contrainte de table : PK composite + FK nommée
CREATE TABLE commandes (
    commande_id  INTEGER NOT NULL,
    ligne_id     INTEGER NOT NULL,
    produit      TEXT    NOT NULL,
    quantite     INTEGER NOT NULL CHECK(quantite > 0),
    prix_unitaire REAL   NOT NULL DEFAULT 0.0,
    CONSTRAINT pk_commande PRIMARY KEY (commande_id, ligne_id)
);

-- Contraintes de colonne avec DEFAULT et CHECK
CREATE TABLE produits (
    produit_id   INTEGER PRIMARY KEY AUTOINCREMENT,
    reference    TEXT    NOT NULL UNIQUE,
    designation  TEXT    NOT NULL,
    prix         REAL    NOT NULL CHECK(prix >= 0),
    stock        INTEGER NOT NULL DEFAULT 0 CHECK(stock >= 0),
    actif        INTEGER NOT NULL DEFAULT 1 CHECK(actif IN (0, 1))
);
""")
print("Tables avec contraintes créées.")

Tables avec contraintes créées.

Démonstration des contraintes avec SQLite#

import traceback

conn2 = sqlite3.connect(":memory:")
conn2.execute("PRAGMA foreign_keys = ON")
conn2.executescript("""
CREATE TABLE categories (
    cat_id  INTEGER PRIMARY KEY,
    nom     TEXT    NOT NULL UNIQUE
);
CREATE TABLE articles (
    art_id  INTEGER PRIMARY KEY AUTOINCREMENT,
    titre   TEXT    NOT NULL,
    prix    REAL    NOT NULL CHECK(prix >= 0),
    stock   INTEGER NOT NULL DEFAULT 0,
    cat_id  INTEGER REFERENCES categories(cat_id) ON DELETE RESTRICT
);
INSERT INTO categories VALUES (1, 'Livres'), (2, 'Électronique');
INSERT INTO articles (titre, prix, stock, cat_id) VALUES
    ('Python avancé', 39.90, 15, 1),
    ('Clavier mécanique', 89.00, 8, 2);
""")

tests = [
    ("NOT NULL viole", "INSERT INTO articles (prix, stock, cat_id) VALUES (10.0, 5, 1)"),
    ("CHECK prix négatif", "INSERT INTO articles (titre, prix, stock, cat_id) VALUES ('Test', -5.0, 0, 1)"),
    ("FK inexistante", "INSERT INTO articles (titre, prix, stock, cat_id) VALUES ('Gadget', 20.0, 1, 99)"),
    ("UNIQUE viole", "INSERT INTO categories VALUES (3, 'Livres')"),
]

for description, sql in tests:
    try:
        conn2.execute(sql)
        print(f"[OK] {description} — aucune erreur (inattendu)")
    except sqlite3.IntegrityError as e:
        print(f"[IntegrityError] {description} : {e}")
    except sqlite3.OperationalError as e:
        print(f"[OperationalError] {description} : {e}")

[IntegrityError] NOT NULL viole : NOT NULL constraint failed: articles.titre
[IntegrityError] CHECK prix négatif : CHECK constraint failed: prix >= 0
[IntegrityError] FK inexistante : FOREIGN KEY constraint failed
[IntegrityError] UNIQUE viole : UNIQUE constraint failed: categories.nom

Valeurs NULL : logique ternaire#

Définition 38 (La valeur NULL et la logique ternaire)

NULL représente une valeur inconnue ou absente. SQL utilise une logique ternaire (TRUE, FALSE, UNKNOWN) pour évaluer les expressions contenant NULL :

NULL = NULL → UNKNOWN (pas TRUE !)
NULL AND TRUE → UNKNOWN
NULL OR TRUE → TRUE
NOT NULL → UNKNOWN

La clause WHERE ne retient que les lignes où la condition est TRUE — les lignes UNKNOWN sont exclues, comme les lignes FALSE.

Exemple 10 (Pièges liés à NULL)

Les erreurs courantes avec NULL :

WHERE col = NULL ne retourne jamais rien — il faut WHERE col IS NULL.
WHERE col <> 'valeur' exclut les lignes où col est NULL.
COUNT(col) ne compte pas les NULL, mais COUNT(*) compte toutes les lignes.
SUM, AVG, MAX, MIN ignorent les NULL.

COALESCE, NULLIF, IFNULL#

Définition 39 (Fonctions de gestion de NULL)

COALESCE(expr1, expr2, ..., exprN) retourne la première valeur non-NULL de la liste. Standard SQL.
NULLIF(expr1, expr2) retourne NULL si expr1 = expr2, sinon retourne expr1. Utile pour éviter les divisions par zéro.
IFNULL(expr, valeur_defaut) est spécifique à SQLite/MySQL : retourne valeur_defaut si expr est NULL. Équivalent à COALESCE(expr, valeur_defaut).

conn3 = sqlite3.connect(":memory:")
conn3.executescript("""
CREATE TABLE contacts (
    id       INTEGER PRIMARY KEY,
    nom      TEXT NOT NULL,
    mobile   TEXT,
    fixe     TEXT,
    email    TEXT
);
INSERT INTO contacts VALUES
    (1, 'Alice',   '06-11',  '01-22', 'alice@x.fr'),
    (2, 'Bob',     NULL,     '01-33', NULL),
    (3, 'Charlie', '06-44',  NULL,    'charlie@x.fr'),
    (4, 'Diana',   NULL,     NULL,    NULL);
""")

query = """
SELECT
    nom,
    mobile,
    fixe,
    COALESCE(mobile, fixe, 'Aucun')    AS premier_contact,
    IFNULL(email, 'email inconnu')     AS email_affiche,
    NULLIF(mobile, fixe)               AS mobile_si_different
FROM contacts
"""
df = pd.read_sql(query, conn3)
print(df.to_string(index=False))

    nom mobile  fixe premier_contact email_affiche mobile_si_different
  Alice  06-11 01-22           06-11    alice@x.fr               06-11
    Bob    NaN 01-33           01-33 email inconnu                 NaN
Charlie  06-44   NaN           06-44  charlie@x.fr               06-44
  Diana    NaN   NaN           Aucun email inconnu                 NaN

# Démonstration NULLIF pour éviter division par zéro
conn4 = sqlite3.connect(":memory:")
conn4.executescript("""
CREATE TABLE ventes (produit TEXT, ventes_n INTEGER, ventes_n1 INTEGER);
INSERT INTO ventes VALUES ('A', 120, 100), ('B', 0, 0), ('C', 50, 0), ('D', 80, 90);
""")

query = """
SELECT
    produit,
    ventes_n,
    ventes_n1,
    ROUND(CAST(ventes_n AS REAL) / NULLIF(ventes_n1, 0) * 100 - 100, 1) AS evolution_pct
FROM ventes
"""
df = pd.read_sql(query, conn4)
print(df.to_string(index=False))
print("(NULL = division par zéro évitée par NULLIF)")

produit  ventes_n  ventes_n1  evolution_pct
      A       120        100           20.0
      B         0          0            NaN
      C        50          0            NaN
      D        80         90          -11.1
(NULL = division par zéro évitée par NULLIF)

Résumé#

Remarque 12

Bonnes pratiques à retenir :

Choisir le type le plus restrictif qui convient : INTEGER plutôt que TEXT pour les identifiants, NUMERIC pour les montants.
Mettre NOT NULL partout où une valeur est sémantiquement obligatoire.
Documenter les contraintes avec CONSTRAINT nom_contrainte pour des messages d’erreur lisibles.
Activer les clés étrangères avec SQLite (PRAGMA foreign_keys = ON) à chaque connexion.
Ne jamais tester col = NULL — toujours utiliser col IS NULL.

Ce chapitre a couvert les types de données SQL (entiers, décimaux, texte, booléens, dates, binaires), leurs différences entre SQLite, PostgreSQL et MySQL, les six contraintes fondamentales (NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT), la logique ternaire de NULL, et les fonctions COALESCE, NULLIF, IFNULL pour gérer les valeurs manquantes.