--- jupytext: text_representation: extension: .md format_name: myst format_version: 0.13 jupytext_version: 1.16.0 kernelspec: name: python3 display_name: Python 3 --- # Types de données et contraintes ```{code-cell} python :tags: [hide-input] import sqlite3 import pandas as pd import matplotlib.pyplot as plt import matplotlib.patches as mpatches import numpy as np import seaborn as sns sns.set_theme(style="whitegrid", palette="muted", font_scale=1.1) ``` ## 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 ```{prf:definition} Types entiers :label: ch03-def-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 ```{prf:definition} Types à virgule flottante et décimaux :label: ch03-def-numeriques - `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`. ``` ```{prf:remark} :label: ch03-rem-float-argent 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 ```{prf:definition} Types texte :label: ch03-def-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 ```{prf:definition} Types BLOB, BOOLEAN, DATE et DATETIME :label: ch03-def-autres-types - `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 ```{code-cell} python :tags: [hide-input] # Tableau comparatif des types par SGBDR types_data = { "Type SQL standard": [ "INTEGER", "BIGINT", "NUMERIC(p,s)", "FLOAT/REAL", "VARCHAR(n)", "TEXT", "BOOLEAN", "DATE", "TIMESTAMP", "JSON", "BLOB/BYTEA" ], "SQLite": [ "INTEGER (8 oct)", "INTEGER (8 oct)", "NUMERIC (texte)", "REAL (8 oct)", "TEXT", "TEXT", "INTEGER (0/1)", "TEXT (ISO 8601)", "TEXT (ISO 8601)", "TEXT", "BLOB" ], "PostgreSQL": [ "INTEGER (4 oct)", "BIGINT (8 oct)", "NUMERIC(p,s)", "DOUBLE PRECISION", "VARCHAR(n)", "TEXT", "BOOLEAN", "DATE", "TIMESTAMP [WITH TZ]", "JSON / JSONB", "BYTEA" ], "MySQL": [ "INT (4 oct)", "BIGINT (8 oct)", "DECIMAL(p,s)", "FLOAT / DOUBLE", "VARCHAR(n)", "TEXT / LONGTEXT", "TINYINT(1)", "DATE", "DATETIME / TIMESTAMP", "JSON", "BLOB / LONGBLOB" ], } df_types = pd.DataFrame(types_data) fig, ax = plt.subplots(figsize=(14, 7)) ax.axis("off") table = ax.table( cellText=df_types.values, colLabels=df_types.columns, cellLoc="left", loc="center", ) table.auto_set_font_size(False) table.set_fontsize(8.5) table.auto_set_column_width(col=list(range(len(df_types.columns)))) # Colorier l'en-tête for j in range(len(df_types.columns)): table[0, j].set_facecolor("#1565c0") table[0, j].set_text_props(color="white", fontweight="bold") # Alterner les couleurs de lignes for i in range(1, len(df_types) + 1): for j in range(len(df_types.columns)): table[i, j].set_facecolor("#e3f2fd" if i % 2 == 0 else "white") ax.set_title("Comparaison des types par SGBDR", fontsize=13, fontweight="bold", pad=20) plt.savefig("_static/ch03_types_sgbdr.png", dpi=120, bbox_inches="tight") plt.show() ``` --- ## 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 ```{prf:definition} Contrainte NOT NULL :label: ch03-def-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 ```{prf:definition} Contrainte UNIQUE :label: ch03-def-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 ```{prf:definition} Contrainte PRIMARY KEY :label: ch03-def-pk `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 ```{prf:definition} Contrainte FOREIGN KEY :label: ch03-def-fk `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. ``` ```{prf:remark} :label: ch03-rem-fk-sqlite 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 ```{prf:definition} Contrainte CHECK :label: ch03-def-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 ```{prf:definition} Contrainte DEFAULT :label: ch03-def-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 ```{prf:definition} Niveaux de déclaration des contraintes :label: ch03-def-niveaux-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. ``` ```{code-cell} python 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.") ``` --- ## Démonstration des contraintes avec SQLite ```{code-cell} python 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}") ``` --- ## Valeurs NULL : logique ternaire ```{prf:definition} La valeur NULL et la logique ternaire :label: ch03-def-null `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`. ``` ```{prf:example} Pièges liés à NULL :label: ch03-ex-null-pieges 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 ```{prf:definition} Fonctions de gestion de NULL :label: ch03-def-fonctions-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)`. ``` ```{code-cell} python 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)) ``` ```{code-cell} python # 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)") ``` --- ## Résumé ```{prf:remark} :label: ch03-rem-bonne-pratique-types Bonnes pratiques à retenir : 1. Choisir le type le plus restrictif qui convient : `INTEGER` plutôt que `TEXT` pour les identifiants, `NUMERIC` pour les montants. 2. Mettre `NOT NULL` partout où une valeur est sémantiquement obligatoire. 3. Documenter les contraintes avec `CONSTRAINT nom_contrainte` pour des messages d'erreur lisibles. 4. Activer les clés étrangères avec SQLite (`PRAGMA foreign_keys = ON`) à chaque connexion. 5. 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.