Merise : du modèle physique au SQL

Le MPD (modèle physique) ajoute au MLD les choix propres au SGBD : types exacts, contraintes, index, moteur de stockage. Il s’exprime sous forme de script SQL. Les exemples utilisent PostgreSQL ; les différences avec MariaDB/MySQL et SQLite sont signalées.

Du MLD au script

MLD du fil rouge :

CLIENT (#num_client, nom, email, adresse)
COMMANDE (#num_commande, date_commande, statut, _num_client)
ARTICLE (#reference, libelle, prix, stock)
LIGNE_COMMANDE (#_num_commande, #_reference, quantite, prix_unitaire)

Choisir les types

Donnée	PostgreSQL	MariaDB / MySQL	SQLite
Identifiant auto-incrémenté	`bigint GENERATED ALWAYS AS IDENTITY`	`BIGINT UNSIGNED AUTO_INCREMENT`	`INTEGER PRIMARY KEY`
Texte court	`varchar(n)` ou `text`	`VARCHAR(n)`	`TEXT`
Montant	`numeric(10,2)`	`DECIMAL(10,2)`	`INTEGER` (centimes) ou `NUMERIC`
Entier	`integer`, `bigint`	`INT`, `BIGINT`	`INTEGER`
Date et heure	`timestamptz`	`DATETIME` / `TIMESTAMP`	`TEXT` (ISO 8601)
Booléen	`boolean`	`BOOLEAN` (alias `TINYINT(1)`)	`INTEGER` (0 ou 1)
Identifiant universel	`uuid`	`UUID` (MariaDB 10.7+)	`TEXT`

Pour les montants, utiliser numeric/DECIMAL (jamais float) ou des centimes en entier.

Script PostgreSQL

CREATE TABLE client (
    num_client  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nom         varchar(100) NOT NULL,
    email       varchar(255) NOT NULL UNIQUE,
    adresse     text,
    cree_le     timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE article (
    reference   varchar(20) PRIMARY KEY,
    libelle     varchar(200) NOT NULL,
    prix        numeric(10,2) NOT NULL CHECK (prix >= 0),
    stock       integer NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

CREATE TABLE commande (
    num_commande  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    date_commande timestamptz NOT NULL DEFAULT now(),
    statut        varchar(20) NOT NULL DEFAULT 'nouvelle'
                  CHECK (statut IN ('nouvelle', 'payee', 'expediee', 'annulee')),
    num_client    bigint NOT NULL
                  REFERENCES client (num_client) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE ligne_commande (
    num_commande   bigint NOT NULL
                   REFERENCES commande (num_commande) ON DELETE CASCADE,
    reference      varchar(20) NOT NULL
                   REFERENCES article (reference) ON DELETE RESTRICT ON UPDATE CASCADE,
    quantite       integer NOT NULL CHECK (quantite >= 1),
    prix_unitaire  numeric(10,2) NOT NULL CHECK (prix_unitaire >= 0),
    PRIMARY KEY (num_commande, reference)
);

CREATE INDEX idx_commande_client ON commande (num_client);
CREATE INDEX idx_commande_date ON commande (date_commande);
CREATE INDEX idx_ligne_reference ON ligne_commande (reference);

Élément	Traduction du MLD
Clé primaire	`PRIMARY KEY` (composée pour `ligne_commande`)
Clé étrangère	`REFERENCES … ON DELETE … ON UPDATE …`
Cardinalité `(1,1)`	`NOT NULL` sur la clé étrangère
Cardinalité `(0,1)`	Clé étrangère sans `NOT NULL`
Unicité de l’e-mail	`UNIQUE`
Règle « quantité au moins 1 »	`CHECK (quantite >= 1)`
Liste de statuts	`CHECK (statut IN (…))` ou table de référence

Actions référentielles : le bon choix

Table liée	Action	Raison
`commande.num_client`	`RESTRICT`	On ne supprime pas un client qui a des commandes
`ligne_commande.num_commande`	`CASCADE`	Les lignes n’existent que dans leur commande
`ligne_commande.reference`	`RESTRICT`	L’historique doit rester cohérent

Éviter CASCADE sur des tables métier importantes : une suppression accidentelle se propagerait.

Index

PostgreSQL crée automatiquement un index pour PRIMARY KEY et UNIQUE, pas pour les clés étrangères : créer les index des clés étrangères utilisées dans les jointures et les filtres (idx_commande_client).

À indexer	Raison
Clés étrangères	Jointures et suppressions en cascade
Colonnes de filtre fréquentes (`date_commande`, `statut`)	`WHERE` rapides
Colonnes de tri	`ORDER BY`

Trop d’index ralentissent les écritures et consomment de l’espace : mesurer d’abord (voir PostgreSQL : index et performances et MariaDB : index et performances).

Variante MariaDB / MySQL

CREATE TABLE client (
    num_client  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nom         VARCHAR(100) NOT NULL,
    email       VARCHAR(255) NOT NULL UNIQUE,
    adresse     TEXT,
    cree_le     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE commande (
    num_commande   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    date_commande  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    statut         ENUM('nouvelle', 'payee', 'expediee', 'annulee') NOT NULL DEFAULT 'nouvelle',
    num_client     BIGINT UNSIGNED NOT NULL,
    CONSTRAINT fk_commande_client FOREIGN KEY (num_client)
        REFERENCES client (num_client) ON DELETE RESTRICT ON UPDATE CASCADE,
    INDEX idx_commande_client (num_client)
) ENGINE=InnoDB;

Différences : AUTO_INCREMENT, ENUM, moteur InnoDB (obligatoire pour les clés étrangères), jeu de caractères utf8mb4, index créés avec la table. Les types des colonnes d’une clé étrangère doivent être identiques à ceux de la colonne référencée (même signe UNSIGNED). Voir MariaDB : bases et tables.

Variante SQLite

PRAGMA foreign_keys = ON;                 -- à activer à CHAQUE connexion

CREATE TABLE client (
    num_client INTEGER PRIMARY KEY,
    nom        TEXT NOT NULL,
    email      TEXT NOT NULL UNIQUE,
    adresse    TEXT
);

CREATE TABLE commande (
    num_commande  INTEGER PRIMARY KEY,
    date_commande TEXT NOT NULL DEFAULT (datetime('now')),
    statut        TEXT NOT NULL DEFAULT 'nouvelle' CHECK (statut IN ('nouvelle', 'payee', 'expediee', 'annulee')),
    num_client    INTEGER NOT NULL REFERENCES client (num_client) ON DELETE RESTRICT
);

SQLite n’applique pas les clés étrangères tant que PRAGMA foreign_keys = ON n’a pas été exécuté sur la connexion. Voir SQLite : tables.

Données d’essai

INSERT INTO client (nom, email, adresse) VALUES
    ('Alice', 'alice@exemple.com', '12 rue des Lilas, Lyon'),
    ('Bob', 'bob@exemple.com', '3 place du Marché, Nantes');

INSERT INTO article (reference, libelle, prix, stock) VALUES
    ('A12', 'Clavier', 49.00, 20),
    ('B07', 'Souris', 25.00, 50);

INSERT INTO commande (num_client) VALUES (1);

INSERT INTO ligne_commande (num_commande, reference, quantite, prix_unitaire) VALUES
    (1, 'A12', 1, 49.00),
    (1, 'B07', 2, 25.00);

Vérifier que le modèle protège les données

-- Refusé : le client n'existe pas (clé étrangère)
INSERT INTO commande (num_client) VALUES (999);

-- Refusé : e-mail déjà utilisé (UNIQUE)
INSERT INTO client (nom, email) VALUES ('Alicia', 'alice@exemple.com');

-- Refusé : quantité invalide (CHECK)
INSERT INTO ligne_commande (num_commande, reference, quantite, prix_unitaire) VALUES (1, 'A12', 0, 49.00);

-- Refusé : le client a des commandes (RESTRICT)
DELETE FROM client WHERE num_client = 1;

Chaque contrainte doit être testée par un échec attendu : c’est la meilleure vérification du MPD. La base garantit alors la cohérence quel que soit le code applicatif.

Requêtes de contrôle sur le modèle

-- Total d'une commande
SELECT c.num_commande, SUM(l.quantite * l.prix_unitaire) AS total
FROM commande c
JOIN ligne_commande l USING (num_commande)
GROUP BY c.num_commande;

-- Clients sans commande
SELECT cl.nom FROM client cl
LEFT JOIN commande c ON c.num_client = cl.num_client
WHERE c.num_commande IS NULL;

-- Articles jamais commandés
SELECT a.libelle FROM article a
LEFT JOIN ligne_commande l ON l.reference = a.reference
WHERE l.num_commande IS NULL;

Le total n’est pas stocké : il est calculé (pas de redondance). Voir PostgreSQL : requêtes.

Faire évoluer le schéma : migrations

Une base en production évolue : ajouter une colonne, une table, un index. Ne jamais modifier la base à la main sans trace.

-- migrations/002_ajout_telephone.sql
ALTER TABLE client ADD COLUMN telephone varchar(20);
CREATE INDEX idx_client_nom ON client (nom);

Pratique	Raison
Un fichier SQL numéroté par changement, versionné avec le code	Historique et reproductibilité
Un outil de migration (Flyway, Liquibase, Prisma Migrate, Knex, Alembic)	Applique chaque migration une seule fois et mémorise l’état
Migrations compatibles avec l’ancienne et la nouvelle version du code	Déploiement sans interruption
Sauvegarde avant toute migration	Retour arrière (PostgreSQL : sauvegarde, MariaDB : sauvegarde)
Tester sur une copie des données	Détecter les verrous longs

Ajouter une colonne NOT NULL à une grosse table demande une valeur par défaut et peut verrouiller la table : le faire en plusieurs étapes (ajout nullable, remplissage, ajout de la contrainte).

Appliquer le script

sudo -u postgres createdb boutique
psql -U app -d boutique -f schema.sql
psql -U app -d boutique -c '\dt'
psql -U app -d boutique -c '\d commande'

mariadb -u app -p boutique < schema.sql
sqlite3 boutique.db < schema.sqlite.sql

Voir PostgreSQL : console et MariaDB : console.

Dépannage

Symptôme	Piste
`ERROR: foreign key constraint … cannot be implemented` (MariaDB : `errno 150`)	Types ou signes différents entre clé étrangère et clé référencée, moteur non InnoDB, index manquant
`relation "x" does not exist`	Ordre de création : créer d’abord les tables référencées
`violates foreign key constraint`	Ligne référencée absente, ou suppression d’une ligne encore référencée
`duplicate key value violates unique constraint`	Doublon sur une colonne `UNIQUE` ou `PRIMARY KEY`
`new row … violates check constraint`	Valeur refusée par `CHECK`
Clés étrangères ignorées avec SQLite	`PRAGMA foreign_keys = ON` à chaque connexion
Suppression en cascade inattendue	Revoir `ON DELETE CASCADE` ; utiliser `RESTRICT` pour les tables métier
Migration longue qui bloque	Procéder en plusieurs étapes, hors heures de pointe, sur une copie d’abord

Ubuntu 24.04 et 26.04 : différences à connaître

Les versions des SGBD fournies par Ubuntu diffèrent (PostgreSQL, MariaDB 10.11 et 11.8, MySQL 8.0 et 8.4) : voir les rubriques d’installation pour les versions et les changements ; la syntaxe de ce script fonctionne sur toutes ces versions.