Tables, types et contraintes d'intégrité

Jusqu’ici, vous avez interrogé des tables existantes. Modéliser, c’est l’étape d’avant : décider quelles tables créer, avec quelles colonnes et quelles règles. Un bon modèle rend les requêtes simples et empêche les données incohérentes d’entrer ; un mauvais modèle oblige à corriger les données en permanence.

De l’entité à la table

On part du vocabulaire métier. Une entité est une chose dont on veut garder la trace : un client, un produit, une commande. Elle devient une table. Ses attributs (nom, email, date d’inscription) deviennent des colonnes, chacune avec un type.

CREATE TABLE clients (
    id            INTEGER PRIMARY KEY,            -- identifiant unique
    email         TEXT    NOT NULL UNIQUE,        -- obligatoire et sans doublon
    nom           TEXT    NOT NULL,
    code_postal   TEXT    CHECK (length(code_postal) = 5),
    date_inscription TEXT NOT NULL DEFAULT (date('now')),
    actif         INTEGER NOT NULL DEFAULT 1 CHECK (actif IN (0, 1))
);

Choisir les types

Donnée	PostgreSQL	SQLite
Identifiant	bigint GENERATED ALWAYS AS IDENTITY	INTEGER PRIMARY KEY
Texte	text ou varchar(n)	TEXT
Montant	numeric(10,2)	INTEGER en centimes
Date / horodatage	date, timestamptz	TEXT au format ISO
Booléen	boolean	INTEGER 0/1

Deux règles : un code postal ou un numéro de téléphone est du texte (un zéro initial ne doit pas disparaître), et un montant ne se stocke jamais en nombre à virgule flottante (real, float), qui produit des erreurs d’arrondi.

SQLite a un typage souple : une colonne INTEGER accepte un texte. Depuis la version 3.37, le mot-clé STRICT en fin de CREATE TABLE impose les types. PostgreSQL, lui, contrôle toujours les types.

La clé primaire

La clé primaire identifie chaque ligne : elle est unique et jamais nulle. Deux options :

• une clé naturelle, issue du métier (numéro de sécurité sociale, code ISO d’un pays) ;
• une clé de substitution, un identifiant sans signification généré par la base.

On préfère généralement la clé de substitution : un email ou un numéro client peuvent changer, et les modifier casserait toutes les références. La donnée métier reste protégée par une contrainte UNIQUE.

Les contraintes

• NOT NULL : la valeur est obligatoire.
• UNIQUE : pas deux lignes avec la même valeur (plusieurs NULL restent autorisés).
• CHECK (condition) : la condition doit être vraie (prix > 0, quantite BETWEEN 1 AND 100).
• DEFAULT valeur : valeur utilisée si l’insertion n’en fournit pas.

Les contraintes s’appliquent à toutes les applications qui écrivent dans la base : script d’import, back-office, API. C’est bien plus sûr qu’une vérification dans un seul formulaire.

Contrôler avant de contraindre

Sur une table déjà remplie, ajouter une contrainte échoue si des lignes la violent. On commence donc par les chercher :

-- Emails en double, sans tenir compte de la casse
SELECT lower(email) AS email_normalise, COUNT(*) AS nb
FROM import_clients
GROUP BY lower(email)
HAVING COUNT(*) > 1;

On corrige ou on fusionne les lignes, puis on ajoute la contrainte. En PostgreSQL : ALTER TABLE clients ADD CONSTRAINT clients_email_key UNIQUE (email);. SQLite ne permet pas d’ajouter une contrainte à une table existante : on recrée la table et on y copie les données.