Le modèle relationnel

Un nom de client, une date de commande et un prix sont des données : des faits enregistrés. Une base de données est un ensemble organisé de données liées. Un système de gestion de bases de données, ou SGBD, est le logiciel qui stocke et gère cet ensemble. PostgreSQL est un SGBD ; la base store d’une boutique est un ensemble qu’il gère.

Stocker chaque information à sa place

Supposons qu’une feuille de calcul répète le numéro de téléphone de Maria Garcia pour chacune de ses 14 ventes. Un changement de numéro nécessite 14 modifications. Une cellule oubliée laisse des enregistrements contradictoires. Dans une base relationnelle, customers contient les coordonnées de Maria une seule fois, sous customer_id 1. Chaque vente enregistre cet identifiant. Mettre à jour son numéro dans une seule ligne client permet aux recherches ultérieures de trouver un seul numéro à jour.

Une base de données relationnelle organise les informations en tables. « Relationnel » vient de relation, le nom mathématique d’une table. E. F. Codd a présenté le modèle en 1970. Sa séparation centrale est utile : les utilisateurs décrivent les données voulues au moyen de tables et de valeurs ; le SGBD se charge de les trouver sur disque.

Règles et accès partagé

Une table possède des colonnes nommées, des types de données et des contraintes. Une contrainte est une règle vérifiée par le SGBD, comme « une commande doit faire référence à un client existant ». Ces vérifications protègent les données partagées lorsque plusieurs applications utilisent la même base. Le contrôle des types varie : PostgreSQL rejette un texte comme 'twelve' dans une colonne de prix numérique, tandis que les tables SQLite ordinaires ont des types souples.

Un SGBD gère aussi les utilisateurs simultanés, les autorisations, les sauvegardes et la restauration. Deux membres du personnel peuvent travailler sur des données partagées sans échanger des copies distinctes d’une feuille de calcul. Les autorisations peuvent permettre à un utilisateur chargé des rapports de lire les ventes tout en limitant les personnes qui peuvent les modifier. Un processus de sauvegarde et de restauration aide à récupérer les données après une panne.

Garder un ensemble de modifications groupées

Une transaction traite plusieurs modifications comme une seule unité : elles sont toutes enregistrées ou aucune ne l’est. Transférer 200 € entre des comptes nécessite de les soustraire du compte courant et de les ajouter au compte d’épargne. Si une panne interrompt le transfert avant sa fin, le SGBD peut annuler la transaction inachevée. Enregistrer uniquement la soustraction laisserait un solde incorrect.

Une feuille de calcul reste utile pour les petits calculs et les mises en page souples. Une base de données fournit une structure définie pour les enregistrements partagés et des règles. Une feuille Excel a une limite fixe de 1 048 576 lignes ; les bases de données gèrent couramment des ensembles bien plus grands, dans les limites du stockage, du matériel et du produit utilisé.

Différentes données et différentes tâches

Les données structurées s’inscrivent dans des colonnes nommées communes à chaque ligne. Les données semi-structurées, comme les documents JSON, peuvent avoir des champs différents d’un enregistrement à l’autre. Les données non structurées, comme l’audio ou les courriels en texte libre, n’ont pas cette structure tabulaire fixe. Les organisations combinent souvent des bases relationnelles avec des bases de documents, des systèmes clé-valeur ou des systèmes de stockage de fichiers.

La charge de travail compte aussi. Un système à charge transactionnelle, aussi appelé OLTP, enregistre les événements quotidiens par de nombreuses lectures et écritures de petite taille : une commande, un paiement, un changement d’adresse. Un système à charge analytique, ou OLAP, lit de nombreux enregistrements stockés pour répondre à des questions comme « Comment les ventes mensuelles ont-elles évolué par région ? » Les deux peuvent utiliser des tables et des clés relationnelles.

SQL est un langage de requête structuré. PostgreSQL, MySQL, SQLite, Microsoft SQL Server et Oracle Database sont des produits qui l’utilisent. SQL Server est un SGBD, et non un autre nom pour SQL lui-même.

Comment une application accède aux données

Dans une architecture client-serveur, un programme serveur gère la base de données. Un client, comme un site web, un outil de rapport ou un éditeur de requêtes, envoie des demandes à ce serveur. PostgreSQL, MySQL, SQL Server et Oracle fonctionnent couramment ainsi. Des outils comme psql, pgAdmin, DBeaver et SQL Server Management Studio permettent d’examiner les bases de données et d’envoyer des demandes.

Un moteur embarqué fonctionne dans une application. SQLite utilise une bibliothèque et un fichier de base de données ordinaire, comme store.db, sans serveur de base de données distinct. Il est courant dans les téléphones, les navigateurs et les applications de bureau. Avec un service cloud géré, comme Azure SQL Database, Amazon RDS ou Google Cloud SQL, un fournisseur exploite les serveurs de base de données. « Géré » indique qui exploite le service ; cela ne remplace pas les tables et les clés par un autre modèle.

Une installation de SGBD peut gérer plusieurs bases de données, comme store et payroll. Une connexion sélectionne la base à utiliser, sous réserve des autorisations.

Indiquer le résultat voulu

SQL est déclaratif : il décrit le résultat demandé tandis que le SGBD choisit comment l’obtenir. Lis cette instruction :

SELECT first_name, city
FROM customers
WHERE state = 'TX';

Elle demande les prénoms et les villes des lignes de clients dont l’État est TX. Elle ne dit rien de la page du disque à lire en premier. Les mots-clés en majuscules facilitent la lecture de l’instruction ; select et SELECT ont le même sens. Un point-virgule termine normalement une instruction.

Reconnaître le rôle de l’instruction

Certains ouvrages placent SELECT dans un groupe distinct, le langage de requête de données. La distinction utile est le rôle réel de l’instruction : ajouter une colonne modifie la structure ; ajouter un client modifie les données.

SQL est devenu une norme ANSI en 1986 et est normalisé au niveau international sous la référence ISO/IEC 9075. Les produits ajoutent leurs propres dialectes. Une limite de dix lignes s’écrit LIMIT 10 dans SQLite, PostgreSQL et MySQL, TOP 10 dans SQL Server ou FETCH FIRST 10 ROWS ONLY en SQL standard et dans Oracle.

Lire le texte et les autres objets de base de données

Les littéraux textuels utilisent des apostrophes : 'TX'. Le SQL standard utilise des guillemets doubles pour les identifiants, comme les noms de colonnes. Une apostrophe à l’intérieur du texte est doublée : 'O''Brien' représente O'Brien.

Une vue est une requête enregistrée utilisée comme une table ; une vue ordinaire ne possède aucune ligne stockée en propre. Un index est une structure de recherche qui peut aider à trouver rapidement des lignes. La plupart des systèmes indexent automatiquement les clés primaires. Les procédures stockées et les fonctions sont du code enregistré qui peut être exécuté par son nom.

Une table stocke des informations sur un type d’éléments. Chaque ligne de customers décrit un client ; chaque colonne contient une propriété. Leur intersection contient une valeur.

Lire un enregistrement sur toute sa ligne

Voici une sélection de colonnes de clients :

Trouve customer_id 4, puis lis city : la ville de Wei est Seattle. Pour trouver qui habite à Denver, lis le nom sur la ligne de Denver : James Wilson. Une ligne voisine décrit un autre client.

La table complète comporte huit colonnes : customer_id, first_name, last_name, email, phone, city, state et zip_code. Le numéro de téléphone de James est NULL : la valeur est manquante, mais sa ligne possède toujours la colonne de téléphone.

Associer les termes

Un n-uplet est une ligne entière, et non une cellule. Un attribut désigne une propriété commune à toutes les lignes. « Enregistrement 4 » peut désigner un identifiant ou une position à l’affichage ; vérifie lequel, car les positions peuvent changer.

Compter les éléments de la table

Le degré compte les colonnes ; la cardinalité d’une table compte les lignes. La table complète a pour degré 8 et pour cardinalité 4. L’affichage ci-dessus montre six colonnes sans changer le degré de la table.

Ajouter un client porte la cardinalité à 5 ; le degré reste égal à 8. Ajouter birth_date porte le degré à 9 et donne à chaque ligne un emplacement pour cette valeur, même lorsqu’elle est manquante. Un nouveau client ajoute une ligne, et non une colonne.

La cardinalité d’une association, comme un-à-plusieurs, décrit les liens autorisés plutôt que le nombre de lignes d’une table.

Lire les noms qualifiés

Les noms de colonnes sont uniques dans une table. orders.customer_id et customers.customer_id distinguent des colonnes de même nom dans des tables différentes : le point sépare les noms de la table et de la colonne.

La notation snake_case utilise des mots en minuscules séparés par des tirets bas, comme dans zip_code. En SQL, les noms contenant des espaces doivent être placés entre guillemets. Les noms de tables au singulier ou au pluriel conviennent tous deux ; applique une convention cohérente.

Une table relationnelle possède une structure logique qui dépasse son apparence rectangulaire. Dans le modèle formel, c’est un ensemble de lignes. Un ensemble ne contient pas de doublons et n’a pas d’ordre. Les colonnes sont identifiées par leur nom : déplacer une colonne à l’affichage ne change donc pas l’information qu’elle représente.

L’identité ne vient pas de la position

Un SGBD peut renvoyer les lignes dans un ordre différent après des mises à jour, des suppressions ou un changement dans sa façon de lire le stockage. « La troisième ligne » n’est pas un moyen durable d’identifier un client. customer_id 3 identifie toujours Aisha, même lorsque sa ligne apparaît en premier. Une requête qui exige un ordre précis le demande avec ORDER BY.

Les tables SQL diffèrent des relations formelles : sans clé primaire ou autre contrainte d’unicité adaptée, elles peuvent contenir des lignes identiques. Deux copies de ('Coffee mug', 12.50) ne comportent aucune valeur permettant de les distinguer dans ces colonnes. Une clé primaire donne à chaque ligne un identifiant unique et empêche les lignes entièrement identiques.

Une colonne, un domaine

Un domaine est l’ensemble des valeurs autorisées dans une colonne. Une quantité a un type entier et peut aussi nécessiter une règle imposant qu’elle soit positive. Un code d’État est du texte, mais toute valeur textuelle n’est pas un code d’État valide. Les types de données et les contraintes décrivent les valeurs autorisées au SGBD.

Toutes les lignes partagent les mêmes colonnes. Une commande ne peut pas recevoir une colonne supplémentaire pour un mot de cadeau qui lui soit propre. La table peut avoir une colonne gift_note pour chaque commande, avec NULL lorsqu’aucun mot n’a été enregistré. Les informations facultatives ont elles aussi un emplacement défini.

Séparer les valeurs individuelles

Une valeur atomique est une seule valeur pour l’usage de la colonne. Une cellule de téléphone contenant « 512-555-0147, 512-555-0199 » contient une liste de deux numéros. Il devient difficile de trouver, de vérifier ou de supprimer un seul numéro. Une table customer_phones peut donner à chaque numéro de téléphone sa propre ligne, avec customer_id pour indiquer à qui il appartient.

Atomique ne signifie pas que toute valeur textuelle doit être divisée en ses plus petits éléments. Un nom de ville peut contenir plusieurs mots et rester une seule valeur de ville. La question est de savoir si la cellule stocke une seule information du type représenté par la colonne ou un ensemble qui devrait être traité séparément.

Examiner avec un regard critique un export de feuille de calcul

Une feuille de ventes peut comporter un en-tête répété au milieu, une ligne en bas portant la mention « TOTAL » et un prix écrit « 12,50 (promotion) ». Ces choix de présentation sont utiles à une personne, mais ils mélangent différents types de contenu dans les mêmes colonnes.

Pour la lire comme une table relationnelle :

1. Traite les noms de colonnes comme l’en-tête, et non comme des enregistrements de vente.
2. Consacre chaque ligne de données à une vente ; calcule le total à partir de ces lignes.
3. Stocke le prix numérique séparément d’une note sur la promotion.
4. Donne une clé à chaque vente pour que sa ligne puisse être identifiée.

Une ligne de total mêlée aux ventes pourrait être comptée comme une vente supplémentaire ou ajoutée une nouvelle fois au total. Une note mélangée à un prix numérique empêche les opérations numériques ordinaires.

Tables logiques et stockage physique

Une table est la vue logique de l’utilisateur. Le SGBD peut stocker ses données dans des fichiers, des pages et des index organisés pour un accès efficace. L’indépendance des données signifie que des modifications de cette organisation physique n’imposent pas de changer les noms des tables, le sens des colonnes ou les requêtes utilisées par les applications.

Un type de données indique au SGBD quel genre de valeur contient une colonne et comment la traiter. L’addition numérique, la comparaison de textes et le classement des dates sont des opérations différentes. Un nom de type familier peut aussi se comporter différemment selon le produit de base de données.

Nombres entiers, exacts et approchés

Dans PostgreSQL, MySQL et SQL Server, INTEGER va de −2 147 483 648 à 2 147 483 647. BIGINT atteint 9 223 372 036 854 775 807 ; le stockage des entiers de SQLite peut aussi atteindre cette plage.

Pour les décimaux, la précision est le nombre total de chiffres et l’échelle est le nombre de chiffres après la virgule. DECIMAL(8, 2) laisse six chiffres avant la virgule : son maximum est de 999 999,99. Le prix 12,50 convient ; 1 234 567,00 ne convient pas. PostgreSQL, MySQL, SQL Server et Oracle stockent les décimaux exactement. SQLite accepte cette déclaration mais n’impose ni sa précision ni son échelle, et peut stocker les fractions en virgule flottante binaire.

La virgule flottante donne une approximation de nombreuses fractions décimales. En double précision, l’addition de 0,1 et de 0,2 donne 0,30000000000000004. SQLite traite ces littéraux décimaux comme des nombres à virgule flottante. PostgreSQL traite les littéraux décimaux écrits sans précision de type comme des nombres exacts : le même calcul écrit peut donc y être exact ; des valeurs explicitement déclarées en double précision montrent l’approximation.

Texte, dates et indicateurs

CHAR(n) est un texte de longueur fixe ; PostgreSQL et SQL Server complètent les valeurs plus courtes avec des espaces. VARCHAR(n) accepte des longueurs variables jusqu’à une limite. SQLite ignore les deux paramètres de longueur et ne complète pas les valeurs. TEXT contient du texte long dans SQLite, PostgreSQL et MySQL. SQL Server utilise VARCHAR(MAX) ou NVARCHAR(MAX) pour cet usage ; son ancien type TEXT est obsolète.

Un DATE contient une date du calendrier, un TIME une heure et un TIMESTAMP du SQL standard une date et une heure. Écris les dates dans l’ordre année-mois-jour, comme '2026-03-02', pour éviter de confondre le 2 mars et le 3 février. Les dates stockées dans un type date ont un format interne ; l’application choisit leur format d’affichage. SQLite n’a pas de type date dédié et stocke couramment les dates sous forme de texte ISO. SQL Server utilise DATETIME2 pour une date et une heure : son TIMESTAMP désigne ROWVERSION, un compteur de version binaire.

Un BOOLEAN représente TRUE ou FALSE, ou NULL lorsque la valeur manque. PostgreSQL possède un type booléen ; SQL Server utilise BIT, MySQL traite BOOLEAN comme un petit entier et SQLite stocke ces indicateurs sous forme de 1 et 0. Les types binaires comme BLOB ou BYTEA de PostgreSQL stockent des octets, notamment de petites images. Les fichiers plus volumineux sont souvent stockés séparément, avec un chemin ou une URL dans une table.

L’affinité de type de SQLite

Dans les tables SQLite ordinaires, un type déclaré est une préférence appelée affinité de type. Une colonne INTEGER convertit '42' en nombre 42, mais peut conserver 'abc' comme texte. Une table SQLite STRICT rejette un texte qui ne peut pas devenir un entier valide.

Pars du sens d’une valeur et de l’usage qui en sera fait. Un code postal américain contient des chiffres, mais personne n’additionne des codes postaux pour obtenir un total qui a un sens. Une quantité contient aussi des chiffres, et additionner des quantités est utile. L’apparence seule guide mal le choix d’un type.

Poser cinq questions

1. Quelles sont les valeurs courantes et quelles valeurs extrêmes doivent pouvoir être stockées ?
2. Les valeurs serviront-elles à des calculs ?
3. Doivent-elles être exactes ou une approximation est-elle acceptable ?
4. Comment doivent-elles être triées et comparées ?
5. Quelle taille convient aux valeurs futures réalistes ?

Montants et mesures

Les montants doivent être exacts. Dans PostgreSQL, MySQL et SQL Server, un prix peut utiliser DECIMAL(8, 2). Trois articles à 12,50 € coûtent exactement 37,50 €. Le stockage en virgule flottante binaire peut laisser de minuscules erreurs après des additions répétées, ce qui rend les comparaisons exactes peu fiables. Dans SQLite, déclarer DECIMAL ne fournit pas de stockage décimal exact ; utilise des centimes entiers pour les montants exacts : 12,50 € est ainsi stocké sous la forme 1 250.

Une mesure comme un poids peut utiliser REAL lorsque de petits écarts d’arrondi sont acceptables. Choisis l’unité comme élément du sens de la colonne : weight_lb indique les livres dans son nom. Un type numérique seul n’enregistre pas cette unité.

La devise doit aussi être précisée. Si une table mélange des devises, ajoute currency_code, avec des codes comme USD, EUR ou JPY. Le nombre de décimales varie selon la devise et les montants en devises différentes doivent être convertis avant de pouvoir être additionnés.

Les codes et les noms sont du texte

Utilise du texte pour les codes postaux américains, les numéros de téléphone, les numéros de compte et les codes de stock. Le code postal de Boston '02108' perd son zéro initial lorsqu’il est converti en entier. VARCHAR(10) peut aussi contenir un code ZIP+4, comme '02108-1234'. Les numéros de téléphone peuvent contenir un signe plus, des espaces et des tirets. Ces caractères font partie de la représentation d’un identifiant et ne sont pas des signes arithmétiques.

Prévois assez de place pour les noms et les adresses réels. « Christopher Montgomery » comporte 22 caractères : VARCHAR(10) est donc trop court. PostgreSQL et SQL Server le rejettent ; MySQL le rejette en mode strict et peut le tronquer sinon. SQLite le stocke, car les longueurs de texte déclarées sont ignorées. Un champ de longueur variable suffisamment grand évite de rejeter des noms courants sans réserver sa longueur maximale pour chaque ligne.

Le tri révèle un mauvais choix

Le tri de texte compare les caractères. Des nombres stockés sous forme de texte peuvent apparaître dans l’ordre '1', '10', '2', '25', '3'. Une colonne numérique trie ces valeurs selon leur ordre numérique. Choisir le texte pour chaque champ reporte les vérifications et les conversions sur les calculs ultérieurs.

Les dates présentent un piège similaire. Les dates américaines sous forme de texte placent '12/01/2025' après '03/02/2026' dans l’ordre textuel, bien que décembre 2025 soit antérieur. Utilise un type date ou, dans SQLite, du texte ISO mis en forme de façon cohérente afin de préserver l’ordre chronologique.

Examiner ensemble les types d’un produit

Un produit peut utiliser product_id INTEGER, sku VARCHAR(20), name VARCHAR(100), price DECIMAL(8, 2), weight_lb REAL, in_stock BOOLEAN et added_on DATE. Chaque type répond à un usage : identification, stockage d’un code, nom, montant exact, mesure, indicateur ou date. Dans SQLite, stocke le prix en centimes entiers et la date sous forme de texte ISO.

NULL marque une valeur manquante. Il diffère de zéro, de la chaîne vide '' et du texte 'NULL'. Une remise connue de 0 signifie qu’il n’y a pas de remise ; une remise NULL signifie que le montant n’est pas enregistré. Une colonne qui autorise NULL accepte les données manquantes, tandis que NOT NULL impose une valeur.

Une valeur manquante peut avoir plusieurs sens

Aucun numéro de téléphone n’est enregistré pour James Wilson. La commande 102 n’a pas de date d’expédition parce qu’elle n’a pas encore été expédiée. Un client qui est une entreprise peut ne pas avoir de nom de famille applicable. Tous ces cas peuvent être représentés par NULL, mais NULL n’indique pas la raison applicable. Un dictionnaire de données devrait préciser le sens pour chaque colonne.

Une chaîne vide est une valeur textuelle présente dans PostgreSQL et SQLite. Oracle traite actuellement la chaîne vide comme NULL. Les outils de requête peuvent afficher les deux sous forme d’une cellule qui semble vide : l’apparence seule ne permet donc pas de les distinguer.

Les comparaisons peuvent être inconnues

Les comparaisons SQL ont trois résultats possibles : TRUE, FALSE et UNKNOWN. Une comparaison faisant intervenir NULL, y compris NULL = NULL, vaut UNKNOWN. On ne sait pas si deux informations manquantes ont la même valeur.

Une clause WHERE ne conserve que les résultats TRUE. Lis ces conditions :

• phone IS NULL trouve les numéros de téléphone manquants.
• phone IS NOT NULL trouve les numéros de téléphone enregistrés.
• phone = NULL donne UNKNOWN et ne trouve aucune ligne.

La négation d’une comparaison ne récupère pas une valeur manquante. Ajoute Daniel Brown, dont l’État vaut NULL, aux quatre clients de la boutique. La condition state <> 'TX' conserve CO, MA et WA : trois lignes. Daniel est exclu car la comparaison portant sur son État vaut UNKNOWN. Pour inclure aussi les États manquants, la condition est state <> 'TX' OR state IS NULL.

Combiner les conditions avec soin

Considère que UNKNOWN signifie « pourrait être TRUE ou FALSE ». Un résultat n’est déterminé que si les deux possibilités donnent le même résultat.

Calculs et synthèses

Un calcul arithmétique ordinaire avec NULL donne NULL : 5 + NULL vaut NULL. Le prix d’une ligne multiplié par une quantité manquante est lui aussi manquant, et non égal à zéro. Concaténer du texte avec || et NULL donne NULL dans PostgreSQL et SQLite ; la concaténation d’Oracle traite NULL comme une chaîne vide.

Les fonctions de synthèse ignorent généralement les entrées NULL. Avec cinq clients, dont James et Daniel sans numéro de téléphone, COUNT(*) compte les cinq lignes. COUNT(phone) compte les trois numéros enregistrés. La moyenne de 4, NULL et 2 est de 3, car la somme des deux nombres connus est divisée par deux ; traiter NULL comme zéro donnerait à tort 2.

Utilise NOT NULL pour les informations qui doivent être présentes, comme une date de commande. Cette contrainte n’impose pas un nombre positif ni un texte non vide : ce sont des règles distinctes. Les clés primaires exigent un identifiant non NULL ; les tables SQLite ordinaires comportent une exception historique pour certaines déclarations de clé primaire, donc NOT NULL explicite y est important.

Une clé identifie une ligne au moyen de ses valeurs. Les noms peuvent se répéter ; les identifiants de client distinguent les enregistrements afin de pouvoir les lire, les modifier ou les relier.

Vérifier l’unicité et la minimalité

Une super-clé est une colonne ou une combinaison de colonnes qui identifie une ligne de façon unique. Une clé candidate est une super-clé minimale : retirer une colonne quelconque fait perdre cette garantie. Choisis une clé candidate comme clé primaire ; les autres sont des clés alternatives.

Supposons que employee_id, ssn et email soient chacun obligatoires et uniques. Chacun est une clé candidate. (employee_id, department) est une super-clé, mais pas une clé candidate : employee_id identifie l’employé à lui seul. Un nom de famille répété échoue au test d’unicité.

Quatre clients portant des noms de famille différents ne suffisent pas à établir une clé : un cinquième peut partager un nom. L’unicité doit être vérifiée pour toutes les données futures autorisées. Une répétition réfute une clé proposée ; un échantillon sans répétition nécessite tout de même une règle métier qui la justifie.

Lire la déclaration

Une table possède au maximum une clé primaire. Ses valeurs sont uniques et ses colonnes doivent être non NULL. Des valeurs stables évitent de modifier les références à répétition. Cherche PRIMARY KEY : ni le nom d’une colonne ni sa position ne la déclarent comme clé primaire.

Si le client 1 existe, un autre client avec la clé 1 est rejeté. Une clé omise nécessite une valeur par défaut ou générée. Dans SQLite, INTEGER PRIMARY KEY génère un entier lorsque la valeur est omise ou vaut NULL. Certaines autres déclarations de clé primaire dans les tables SQLite ordinaires autorisent historiquement NULL ; un NOT NULL explicite, une table STRICT ou la forme de clé primaire entière comble cette lacune.

Lire une clé composite

Dans enrollments(student_id, course_id, term, grade), un étudiant peut suivre plusieurs cours et suivre à nouveau un cours lors d’une autre période. (student_id, course_id, term) identifie une inscription : c’est une clé primaire composite, et non trois clés primaires distinctes.

Les éléments individuels peuvent se répéter ; la combinaison complète ne le peut pas. La plupart des SGBD créent un index sur la clé primaire pour les recherches, mais la clé ne garantit pas l’ordre des résultats d’une requête.

Une clé naturelle provient de données du monde réel, comme le VIN d’un véhicule, l’ISBN d’une édition de livre ou un code d’État. Une clé de substitution existe uniquement pour identifier une ligne, souvent sous la forme d’un entier généré ou d’un UUID.

Choisir un identifiant durable

Privilégie des valeurs uniques, présentes, stables, compactes et pouvant être communiquées sans risque. Les noms se répètent ; les numéros de téléphone et les adresses électroniques changent ou sont partagés. Les numéros de sécurité sociale américains sont sensibles et certains clients n’en ont pas ; les utiliser dans les tables qui y font référence diffuse ces informations.

Un customer_id généré permet à Maria de conserver l’identifiant 1 lorsque ses coordonnées changent. Un state_code naturel, comme TX, peut bien convenir car il est compact, normalisé et utile dans les enregistrements d’adresses.

Protéger séparément les identifiants métier

Une même personne peut s’inscrire deux fois et recevoir deux identifiants de substitution. Les identifiants distinguent les lignes ; ils ne détectent pas les personnes en double.

Si l’adresse électronique doit identifier chaque compte, utilise NOT NULL UNIQUE. UNIQUE seul autorise plusieurs NULL dans PostgreSQL, SQLite et MySQL. SQL Server autorise normalement un seul NULL dans une contrainte d’unicité portant sur une colonne. La présence et l’unicité sont deux règles distinctes.

L’adresse électronique obligatoire et unique est une clé alternative. La modifier laisse inchangé le numéro de client de substitution dans les commandes existantes. Modifier une clé primaire référencée exige aussi que les références des lignes enfants restent cohérentes.

Reconnaître la syntaxe des clés générées

PostgreSQL possède aussi l’ancienne forme SERIAL. Les suppressions, les insertions échouées et les opérations annulées peuvent laisser des trous. Les identifiants 1, 2 et 4 désignent trois lignes, et non quatre. Renuméroter pour combler les trous peut rompre des références.

Reconnaître les UUID

Un UUID est un identifiant de 128 bits, généralement écrit sur 36 caractères avec des tirets : 7c9e6679-7425-40de-944b-e07fc1f90ae7. Des ordinateurs peuvent les générer indépendamment avec un très faible risque de collision, ce qui est utile lorsque des enregistrements de plusieurs endroits doivent être combinés.

Les UUID occupent plus d’espace et sont plus difficiles à lire que de petits entiers. Choisis selon la façon dont les enregistrements sont créés et référencés. Dans les deux cas, une clé est un identifiant : sa valeur maximale n’est pas un nombre de lignes et son ordre ne reflète pas nécessairement le moment des événements.

Une clé étrangère est une colonne ou un groupe de colonnes qui fait référence à une clé primaire ou à une autre clé unique appropriée. La table qui porte la référence est la table enfant ; celle vers laquelle elle pointe est la table parente. Lorsqu’une contrainte est contrôlée, une clé étrangère non NULL doit correspondre à une clé parente existante.

Suivre une valeur entre les tables

orders.customer_id fait référence à customers.customer_id. La commande 102 pointe vers le client 2, James Wilson. Les commandes 101 et 103 pointent toutes deux vers le client 1, Maria Garcia. Les répétitions sont autorisées dans la clé étrangère ; la clé parente référencée est unique.

Cette règle est l’intégrité référentielle. Une commande pour le client 99 est rejetée si le client 99 n’existe pas. Sinon, elle serait orpheline : une ligne enfant sans parent correspondant. Un nom seul ne crée aucune contrainte. Une clé étrangère nommée placed_by peut tout de même faire référence à customer_id ; c’est la référence déclarée qui crée le lien, avec des types correspondants pour les colonnes de clé.

Facultatif ou obligatoire

Un coupon_id qui autorise NULL peut valoir NULL lorsqu’une commande n’a pas de coupon. Un customer_id déclaré NOT NULL impose que chaque commande ait une référence à un client, et la clé étrangère impose que ce client existe. Ces deux contraintes agissent ensemble : l’une exige une valeur et l’autre vérifie la référence.

Les clés étrangères ne sont pas obligées de relier des tables distinctes. Dans employees(employee_id, name, manager_id), manager_id peut faire référence à employee_id dans la même table. La référence au responsable de la personne à la direction générale peut être NULL.

Une règle est nécessaire lorsque le parent change

Avec l’action par défaut, le client 1 ne peut pas être supprimé tant que les commandes 101 et 103 font référence à ce client. Une suppression en cascade d’une commande vers ses lignes peut avoir un sens, car ces lignes appartiennent à cette commande. ON UPDATE précise le comportement correspondant lorsqu’une clé parente change. La cascade ne signifie pas que la suppression d’un enfant supprime son parent.

Références composites et contrôle

Si une ligne de commande est identifiée par (order_id, product_id), une expédition qui fait référence à cette ligne a besoin des deux colonnes. La combinaison doit correspondre à une ligne existante. Faire correspondre séparément une commande et un produit ne suffit pas : ce produit peut ne jamais avoir figuré dans cette commande. Une référence composite obligatoire déclare toutes ses colonnes NOT NULL.

PostgreSQL, SQL Server, Oracle et le moteur InnoDB habituel de MySQL contrôlent les clés étrangères déclarées. Dans SQLite, active et vérifie le contrôle des clés étrangères pour chaque connexion avec PRAGMA foreign_keys = ON; avant de t’y fier. Une contrainte déclarée mais désactivée ne peut pas protéger les lignes. Les clés étrangères ne combinent pas non plus les résultats de requêtes à elles seules ; une requête doit suivre explicitement le lien, généralement par une jointure.

Pour identifier une association, pose deux questions : « Pour une ligne ici, combien de lignes là-bas peuvent être liées ? », puis la question inverse. Tiens compte du minimum comme du maximum. L’échantillon actuel montre ce qui s’est produit ; les contraintes décrivent ce qui est autorisé.

Un client, plusieurs commandes

Un client peut passer zéro ou plusieurs commandes. Chaque commande de la boutique appartient à exactement un client, car orders.customer_id est une clé étrangère non NULL. C’est une association un-à-plusieurs. La clé étrangère se trouve dans orders, du côté plusieurs : chaque commande stocke une référence à un client.

Maria a les commandes 101 et 103 ; James a la commande 102. Aisha et Wei n’en ont aucune. Leur absence de orders est valide. Une clé étrangère impose un parent pour chaque enfant, mais n’impose pas que chaque parent ait un enfant.

Une association un-à-un exige aussi l’unicité

Dans user_profiles(user_id, bio, photo_url), supposons que user_id soit à la fois la clé primaire et une clé étrangère vers users. Chaque profil fait référence à un utilisateur et un utilisateur peut avoir au plus un profil, car la clé du profil ne peut pas se répéter. Un utilisateur peut ne pas encore avoir de profil.

Une clé étrangère sans unicité autoriserait plusieurs profils pour le même utilisateur, ce qui donnerait une association un-à-plusieurs. La contrainte d’unicité change le maximum. Ces tables de profils distinctes permettent de séparer des informations facultatives ou sensibles de l’enregistrement principal de l’utilisateur.

Une association plusieurs-à-plusieurs utilise une table de paires

Une commande peut contenir plusieurs produits et un produit peut apparaître dans plusieurs commandes. Une table d’association, aussi appelée table de liaison, enregistre chaque paire :

order_id fait référence à orders ; product_id fait référence à products. La clé primaire composite (order_id, product_id) autorise le même produit dans différentes commandes, mais empêche qu’une même paire apparaisse deux fois. Deux liens un-à-plusieurs mettent en œuvre l’association plusieurs-à-plusieurs.

quantity et unit_price appartiennent à la paire. Ils décrivent la quantité de ce produit vendue dans cette commande et le prix facturé à ce moment-là. Le prix actuel d’un produit peut changer plus tard ; il ne doit pas modifier le prix facturé à l’époque.

Pour la commande 101, les deux montants de ligne correspondent à deux mugs à 12,50 dollars américains, soit 25,00 dollars américains, et à un chargeur à 8,99 dollars américains. Ensemble, ils coûtent 33,99 dollars américains. La quantité indique combien de mugs ont été achetés ; stocker deux lignes identiques pour les mugs entrerait en conflit avec la clé choisie pour cette table.

Éviter les listes et les emplacements fixes

Une cellule product_ids contenant '11,12' cache plusieurs références dans une seule valeur textuelle. Des colonnes comme product1, product2 et product3 imposent une limite fixe et laissent des emplacements inutilisés. Une table d’association ajoute une ligne pour chaque paire supplémentaire et permet au SGBD de vérifier chaque référence séparément.

Une association réflexive utilise le même principe dans une seule table : plusieurs employés peuvent faire référence au même responsable par manager_id. Le responsable est lui aussi une ligne d’employé. Lis chaque lien comme un rôle afin de distinguer l’identifiant de l’employé de la référence à son responsable.

Un schéma décrit les tables, les colonnes, les types de données et les contraintes. Une instance correspond aux données stockées à un instant donné. Ajouter une commande modifie l’instance ; ajouter une colonne modifie le schéma. Le schéma indique ce qui peut être stocké, tandis que les lignes indiquent ce qui est stocké actuellement.

Lire une description abrégée

customers(customer_id, first_name, last_name, email, phone, city, state, zip_code) présente une table et ses colonnes. Les notes peuvent souligner les clés primaires ou les marquer PK, et marquer les références FK. Par exemple, orders(order_id PK, customer_id FK → customers, order_date, shipped_date) identifie la clé principale et le lien vers le client.

Lire une définition complète de table

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (customer_id),
  order_date DATE NOT NULL,
  shipped_date DATE
);

Lis une colonne à la fois. order_id est la clé primaire entière. customer_id est obligatoire et doit faire référence à un client. order_date est obligatoire. shipped_date peut être NULL. Cette définition donne la structure autorisée, et non une liste des commandes existantes.

Les contraintes répondent à différentes questions :

Une valeur par défaut ne remplace pas une valeur invalide fournie. Si status a pour valeur DEFAULT 'pending', omettre le statut fournit 'pending' ; une valeur fournie doit toujours respecter les autres règles de la colonne.

Combiner les règles d’une ligne de commande

CREATE TABLE order_items (
  order_id INTEGER NOT NULL REFERENCES orders (order_id),
  product_id INTEGER NOT NULL REFERENCES products (product_id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price DECIMAL(8, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

La clé primaire en bas utilise deux colonnes. Une seconde ligne pour la même commande et le même produit est rejetée. Une quantité de 0 ne satisfait pas la vérification. Une quantité manquante ne satisfait pas NOT NULL : la vérification seule autoriserait NULL, car NULL > 0 vaut UNKNOWN et non FALSE. Un NOT NULL explicite sur les deux parties de la clé évite aussi l’exception historique de SQLite qui autorise NULL dans les clés composites. Dans SQLite, utilise des centimes entiers pour un unit_price exact ; la déclaration décimale seule ne fournit pas un stockage exact.

Trouver le schéma et ses significations

Dans l’interface de SQLite, .schema orders affiche la définition ; dans psql de PostgreSQL, \d orders la décrit. Les vues INFORMATION_SCHEMA répertorient les métadonnées de structure dans PostgreSQL, MySQL et SQL Server. SQLite répertorie les objets dans sqlite_schema et les outils graphiques montrent les tables et les colonnes en arborescence.

« Schéma » désigne aussi un espace de noms dans des systèmes comme PostgreSQL et SQL Server. sales.orders désigne la table des commandes dans le schéma des ventes. Un dictionnaire de données fournit les significations que la structure ne peut pas exprimer, comme la devise du prix ou le fait qu’une date d’expédition manquante signifie « non expédié ». Pour voir les lignes, une requête comme SELECT * FROM orders; lit l’instance actuelle.

Un diagramme de schéma représente chaque table par une boîte. Le titre nomme la table ; le contenu liste les colonnes et souvent les types. PK marque les clés primaires et FK les clés étrangères. Les lignes d’association indiquent à quelle clé parente chaque clé étrangère fait référence.

Identifier d’abord le type de ligne

Certains diagrammes utilisent une flèche allant d’une clé étrangère à sa clé référencée. Une flèche de orders.customer_id vers customers.customer_id décrit la direction de la référence. D’autres outils utilisent des symboles aux extrémités pour décrire combien de lignes peuvent être liées. Vérifie la légende du diagramme : la direction d’une flèche et la cardinalité d’une association sont des informations différentes.

Lire les symboles en patte d’oie

Dans la notation en patte d’oie, une barre signifie un, un cercle signifie zéro et une patte à trois branches signifie plusieurs. Chaque extrémité de ligne indique un minimum et un maximum. Le symbole le plus proche de la table donne le maximum ; l’autre donne le minimum.

Lorsque tu pars d’une table, regarde les symboles à côté de l’autre table. Ils décrivent combien de lignes de cette autre table peuvent correspondre à ta ligne de départ.

Examiner les clients et les commandes

La ligne de la boutique porte les symboles « exactement un » à côté de customers et les symboles « zéro ou plusieurs » à côté de orders.

1. Pars d’un client. Regarde l’extrémité des commandes : le client peut avoir zéro ou plusieurs commandes.
2. Pars d’une commande. Regarde l’extrémité des clients : la commande appartient à exactement un client.
3. Vérifie la définition : la clé étrangère non NULL se trouve dans orders, qui est donc du côté plusieurs, et chaque commande nécessite un client valide.

La patte d’oie est à côté de la table qui contient la clé étrangère dont les valeurs se répètent. Lire l’extrémité proche en partant de cette même table inverse le sens. Une référence au client qui autorise NULL changerait l’extrémité des clients en « zéro ou un » ; elle ne limiterait pas chaque client à une commande.

Reconnaître une table d’association

La boutique possède des liens un-à-plusieurs de orders vers order_items et de products vers order_items. Les deux extrémités « plusieurs » se rejoignent à order_items. Ses deux clés étrangères forment la clé primaire. Cette structure permet à une commande de contenir plusieurs produits et à un produit d’apparaître dans plusieurs commandes.

Le diagramme décrit les liens autorisés, et non les nombres actuels. Il permet à Maria d’avoir plusieurs commandes et à Aisha de n’en avoir aucune. Pour savoir combien chacune en a réellement, lis les lignes. Un minimum de un du parent vers l’enfant peut exprimer une règle métier supplémentaire ; une clé étrangère seule n’impose pas qu’un parent ait des enfants.

Reconnaître les styles de diagrammes apparentés

Un diagramme entité-association créé pendant la conception peut montrer les entités de l’activité avant que les tables existent. Les diagrammes de style Chen utilisent des ovales pour les attributs et des losanges pour les associations. Un diagramme de schéma montre les tables réelles et leurs clés déclarées. Les deux aident à communiquer la structure, mais leurs symboles doivent être lus selon leur propre légende.

Localise chaque information demandée et suis les clés qui la relient à l’enregistrement de départ. Les noms peuvent se répéter ou changer ; fais plutôt correspondre les valeurs des clés.

Trouver le client d’une commande

« Où habite le client qui a passé la commande 102 ? » suit ce chemin :

1. Trouve la commande 102 à l’aide de orders.order_id.
2. Lis son customer_id : 2.
3. Trouve le client 2 à l’aide de customers.customer_id.
4. Lis customers.city : Denver.

Le numéro de commande 102 n’est pas l’identifiant du client. Les jointures SQL automatisent ces correspondances de clés ; les positions à l’affichage ne relient pas les enregistrements.

Trouver les produits et un total historique

La commande 101 possède les lignes order_items suivantes. Suis chaque product_id jusqu’à products pour trouver son nom :

Utilise le prix enregistré sur la ligne pour calculer le montant facturé :

• Deux mugs à 12,50 dollars américains coûtent 25,00 dollars américains.
• Un chargeur à 8,99 dollars américains coûte 8,99 dollars américains.
• Total : 33,99 dollars américains.

Le prix actuel d’un produit pourrait donner un coût erroné pour une ancienne commande. La commande 102 contient une lampe à 34,00 dollars américains ; la commande 103 contient quatre mugs à 12,50 dollars américains, soit un total de 50,00 dollars américains.

Interpréter les valeurs manquantes et les règles

La date d’expédition de la commande 102 est NULL, documenté ici comme « non expédiée ». Teste-la avec IS NULL, et non avec une égalité à NULL.

Lis chaque contrainte pertinente lorsque tu évalues une modification :

• Une ligne pour la commande 104 est rejetée si cette commande n’existe pas.
• Une autre ligne avec la clé (101, 11) est rejetée car la paire existe déjà.
• Une quantité de 0 ne satisfait pas la vérification de quantité positive.
• Le comportement par défaut de la clé étrangère bloque la suppression du produit 11 tant que des lignes y font référence.

Une clé inutilisée ne rend pas une ligne valide si sa référence est manquante.

Identifier les nouvelles lignes

Lorsqu’un client existant achète deux produits existants différents, cela ajoute une ligne orders et deux lignes order_items. Le client et les produits existent déjà. Proposer un nouveau produit ajoute une ligne products, sans nécessiter de commande.