Contraintes d'intégrité

Ce que vous saurez faire

Faites refuser par PostgreSQL les lignes incorrectes de tout programme : un client en double, un message pour un billet manquant, une priorité inexistante. Gardez ensuite chaque fait à un seul endroit. Copiez-en un seulement si vous avez une raison.

Avant de commencerCe qu’il faut savoir et avoir sous la mainEnviron 55 minutes

Ce que vous devez savoir

• Les entités, les clés et les relations du service de soutien, et les trois messages orphelins, dans Entités et relations.
• Ce qu’est une contrainte, et pourquoi SQLite exigeait PRAGMA foreign_keys = ON, dans Les concepts relationnels et De SQLite à PostgreSQL.
• Les migrations comme fichiers SQL numérotés, appliqués une seule fois et dans l’ordre, dans De SQLite à PostgreSQL.

Ce qu’il vous faut sur votre ordinateur

• Le projet après la dernière leçon, avec docs/schema.md. python -m pytest affiche 34 passed (34 tests réussis).
• PostgreSQL en cours d’exécution, avec le petit jeu de données chargé.

Cette leçon ajoute deux migrations, 005_integrity_constraints.sql et 006_ticket_message_counts.sql, et un fichier de tests, tests/test_schema.py.

Le comptoir du bureau de poste

Vous envoyez un colis au bureau de poste. La commis regarde le formulaire avant de prendre le colis. La case d’adresse est vide : elle vous rend le formulaire. Le poids est de 40 kg, et la limite est de 30 kg : elle refuse. Le code postal n’existe pas : elle refuse.

Elle vérifie chaque formulaire au comptoir, pour chaque client, avant que quoi que ce soit entre dans le système. Personne ne doit espérer que chaque expéditeur a vérifié le formulaire chez lui. Un formulaire refusé ne laisse aucun colis à moitié envoyé. Soit le colis est dans le système avec un formulaire correct, soit il n’y est pas du tout.

Le problème : la base de données accepte tout

Pour l’instant, les tables du service de soutien ont des clés primaires et rien d’autre. Essayez cinq erreurs dans psql. BEGIN (début) et ROLLBACK (annulation) annulent tout ce qui se trouve entre eux, sans changer vos données. Transactions explique comment.

BEGIN;
INSERT INTO customers (customer_id, name, email, segment, joined_on)
VALUES ('C-0041', 'Olga Olsen', 'Olga.Olsen@Example.com', 'home', '2026-10-09');
INSERT INTO messages (ticket_id, author, body) VALUES ('T-99999', 'agent', 'Hello');
UPDATE tickets SET priority = 4 WHERE ticket_id = 'T-30002';
UPDATE tickets SET status = 'closed' WHERE ticket_id = 'T-30002';
INSERT INTO tickets (ticket_id, subject) VALUES ('30003', '');
SELECT ticket_id, customer_id, priority, status, closed_at
FROM tickets WHERE ticket_id IN ('T-30002', '30003');
ROLLBACK;

BEGIN
INSERT 0 1
INSERT 0 1
UPDATE 1
UPDATE 1
INSERT 0 1
 ticket_id | customer_id | priority | status | closed_at
-----------+-------------+----------+--------+-----------
 30003     |             |          |        |
 T-30002   | C-0022      |        4 | closed |
(2 rows)

ROLLBACK

Chaque instruction a fonctionné, et chacune était incorrecte :

• Un deuxième compte pour Olga Olsen. Son courriel figure déjà dans C-0001, en minuscules. Le service de soutien a maintenant deux clients avec la même adresse.
• Un message pour T-99999, un billet qui n’existe pas : un quatrième orphelin.
• La priorité 4. Le service de soutien a des priorités de 1 à 3. Aucun rapport ni aucune règle ne sait ce que 4 signifie.
• Un billet fermé sans heure de fermeture. Les rapports sur le « temps de fermeture » divisent maintenant par une valeur absente.
• Un billet nommé 30003 sans client, avec un sujet vide et sans état.

Sam demande : « Mais l’API vérifie ses entrées. Pourquoi c’est important? » L’API vérifie une seule porte d’entrée. Le chargeur, un script, psql, une tâche de rapport et le deuxième service de l’an prochain écrivent dans les mêmes tables. Aucun ne passe par l’API. Les données restent pendant des années; les programmes changent. Une règle dans la base protège les données contre chaque programme qui écrit, aussi longtemps qu’elles existent.

L’idée : les contraintes

Une contrainte est une règle dans la définition d’une table que la base vérifie à chaque écriture. Si une écriture enfreint une règle, PostgreSQL refuse l’instruction entière, n’enregistre rien et nomme la règle dans l’erreur. C’est la commis au comptoir.

PostgreSQL en propose cinq types, plus les valeurs par défaut :

• NOT NULL (non nul) : la colonne doit avoir une valeur. Un billet sans client est refusé.
• CHECK (…) (vérification) : une condition sur les valeurs d’une ligne, par exemple priority BETWEEN 1 AND 3.
• UNIQUE (unique) : deux lignes ne peuvent pas avoir la même valeur. Un index unique peut aussi utiliser une expression, comme lower(email).
• PRIMARY KEY (clé primaire) : unique et non nul ensemble : l’identifiant de la dernière leçon.
• FOREIGN KEY … REFERENCES (clé étrangère qui référence) : la valeur doit exister dans une clé d’une autre table. Cela empêche les orphelins et précise aussi ce qui arrive quand l’autre ligne est supprimée.
• DEFAULT (valeur par défaut) n’est pas une règle, mais aide : un nouveau billet reçoit status = 'open' et created_at = now() si le programme ne les envoie pas.

Voici un petit exemple détaillé : les règles que le billet T-30002 doit respecter, avec ses vraies valeurs.

Colonne	Valeur de T-30002	Règle (nom dans la base de données)	Valeur refusée
ticket_id	T-30002	tickets_id_format : T- et 5 ou 6 chiffres	30003
customer_id	C-0022	NOT NULL, et tickets_customer_fk : le client existe	vide, ou C-9999
priority	1	tickets_priority_range : de 1 à 3	4
status, closed_at	open (ouvert), vide	tickets_closed_at_matches_status	closed (fermé) sans heure de fermeture
subject	Charged twice for order LK-182074 (facturé deux fois pour la commande LK-182074)	tickets_subject_not_blank	' '

La base vérifie les règles d’une écriture l’une après l’autre. La première règle non respectée arrête l’écriture :

Chaque écriture passe par les règles de la table. La première règle non respectée arrête l'écriture, et l'erreur nomme la règle. Les quatre exemples sont de vraies erreurs de cette leçon.

Description textuelle du diagramme

Une écriture passe quatre vérifications de suite. NOT NULL échoue quand une valeur obligatoire manque, par exemple quand un nouveau billet n’a pas de customer_id. CHECK échoue quand une valeur enfreint une règle de la ligne, par exemple priority = 4 enfreint tickets_priority_range. UNIQUE échoue quand la valeur existe déjà dans une autre ligne, par exemple Olga.Olsen@Example.com enfreint customers_email_unique. FOREIGN KEY échoue quand la ligne ne pointe vers rien, par exemple un message pour T-99999 enfreint messages_ticket_fk. Une écriture qui passe les quatre est enregistrée, pour chaque programme qui lit la table.

Un nom comme tickets_priority_range vaut la peine d’être saisi. Quand la règle échoue, le message d’erreur la nomme, et ce nom indique ce qui ne va pas.

Les règles de Larkfield : migration 005

Mei écrit les règles dans une migration, pour que chaque copie de la base reçoive les mêmes règles. Commencez là où la dernière leçon s’est terminée : les trois messages orphelins. Elle écrit une première version de migrations/005_integrity_constraints.sql avec une seule règle :

migrations/005_integrity_constraints.sql (first draft)

ALTER TABLE messages
    ADD CONSTRAINT messages_ticket_fk FOREIGN KEY (ticket_id)
    REFERENCES tickets (ticket_id) ON DELETE CASCADE;

Jusqu’ici, scripts.load appliquait les migrations pour vous. L’outil de migration du projet, ticket_api/migrate.py, peut aussi fonctionner seul. Il applique chaque fichier de migrations/ qui n’a pas encore été exécuté :

python -m ticket_api.migrate

psycopg.errors.ForeignKeyViolation: insert or update on table "messages" violates foreign key constraint "messages_ticket_fk"
DETAIL:  Key (ticket_id)=(T-29994) is not present in table "tickets".

Seules les deux dernières lignes de l’erreur sont affichées; une trace d’appels Python les précède. Une nouvelle contrainte doit être respectée par les lignes existantes, donc PostgreSQL a vérifié chaque message. Le premier orphelin, T-29994, a arrêté la migration. Chaque migration s’exécute dans une transaction, donc rien du fichier n’a été conservé : python -m ticket_api.migrate --status affiche encore pending 005_integrity_constraints (migration 005 en attente).

Mei doit décider quoi faire des trois lignes. Elle pourrait les supprimer. Mais ce sont les données de quelqu’un, et « quelqu’un a supprimé les billets à la main » reste une supposition. La première partie de la vraie migration 005 les déplace donc dans leur propre table, pour qu’une personne les vérifie :

migrations/005_integrity_constraints.sql (part 1)

CREATE TABLE orphaned_messages (LIKE messages);
INSERT INTO orphaned_messages
SELECT m.* FROM messages m
WHERE NOT EXISTS (SELECT 1 FROM tickets t WHERE t.ticket_id = m.ticket_id);
DELETE FROM messages m
WHERE NOT EXISTS (SELECT 1 FROM tickets t WHERE t.ticket_id = m.ticket_id);

LIKE messages copie les colonnes de messages. L’instruction INSERT (insertion) conserve les orphelins, et DELETE (suppression) les retire de messages. On peut ensuite ajouter la clé étrangère.

Le reste de 005 ajoute les règles table par table. Voici les parties qui demandent quelques explications :

migrations/005_integrity_constraints.sql (parts of 2 and 3)

ALTER TABLE customers
    ALTER COLUMN email SET NOT NULL,
    ADD CONSTRAINT customers_id_format CHECK (customer_id ~ '^C-[0-9]{4,5}$'),
    ADD CONSTRAINT customers_segment_known CHECK (segment IN ('home', 'trade', 'business'));
-- "Ana@Example.com" and "ana@example.com" are the same address.
CREATE UNIQUE INDEX customers_email_unique ON customers (lower(email));

ALTER TABLE tickets
    ALTER COLUMN status SET DEFAULT 'open',
    ADD CONSTRAINT tickets_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
    ADD CONSTRAINT tickets_priority_range CHECK (priority BETWEEN 1 AND 3),
    -- A ticket has a closing time exactly when it is resolved or closed.
    ADD CONSTRAINT tickets_closed_at_matches_status
        CHECK ((status IN ('resolved', 'closed')) = (closed_at IS NOT NULL));

• ~ '^C-[0-9]{4,5}$' est une expression régulière : C-, puis 4 ou 5 chiffres, et rien d’autre. Le grand jeu de données contient des clients jusqu’à C-20000, donc il faut 5 chiffres.
• UNIQUE sur lower(email), pas sur email. Un simple UNIQUE (email) accepterait Olga.Olsen@Example.com avec olga.olsen@example.com, car les deux textes diffèrent. L’index stocke chaque courriel en minuscules et refuse une deuxième copie.
• tickets_closed_at_matches_status compare deux valeurs vraies ou fausses. « Résolu ou fermé » doit être vrai exactement quand « a une heure de fermeture » est vrai. Un billet ouvert avec une heure de fermeture échoue, comme un billet fermé sans heure de fermeture.

La colonne cost_usd de ai_runs garde son type, real, dans ce module. Ce type arrondit les nombres, et Migrations le change, en expliquant pourquoi.

Ce qui arrive aux lignes enfants quand un billet est supprimé

Une clé étrangère répond aussi à une deuxième question : si le billet disparaît, qu’arrive-t-il aux lignes qui pointent vers lui? Chaque réponse est une décision d’affaires, donc 005 donne à chaque table sa propre règle :

Une suppression, quatre règles. La clé étrangère de chaque table enfant indique ce qui arrive à ses lignes quand leur billet est supprimé.

Description textuelle du diagramme

La suppression du billet T-30001 touche quatre tables. messages a ON DELETE CASCADE, donc les messages du billet sont aussi supprimés. ticket_embeddings a ON DELETE CASCADE, donc son vecteur est aussi supprimé. ai_runs a ON DELETE SET NULL, donc les exécutions restent, leur ticket_id devient vide, et le coût reste dans le total mensuel. attachments n’effectue aucune action, le comportement par défaut. La suppression est donc refusée tant qu’une ligne de fichier pointe vers le billet, car la base ne peut pas supprimer un fichier du stockage objet.

• ON DELETE CASCADE (suppression en cascade) pour les messages et les vecteurs : ils n’ont aucun sens sans leur billet.
• ON DELETE SET NULL (mettre à NULL lors de la suppression) pour les exécutions d’IA : la décision de la dernière leçon. Les coûts mensuels de Priya restent exacts.
• Aucune action pour les pièces jointes. Le fichier lui-même est dans le stockage objet, hors de la base de données. Si la base supprimait la ligne, personne ne saurait que le fichier existe encore. La base refuse donc, et le programme doit d’abord supprimer le fichier. Restauration et conservation construit cette étape.

Chaque fait à un seul endroit, et une copie à dessein

Avant les règles, Mei vérifie une autre question de conception. Pensez à un carnet d’adresses. Vous écrivez l’adresse d’un ami une seule fois dans le carnet. Supposons que vous l’écriviez aussi sur chaque liste de cartes d’anniversaire. Après un déménagement, certaines listes auraient l’ancienne adresse, et vous ne sauriez pas laquelle est correcte.

Une base de données a le même problème. Voici une table plate inventée, du genre qu’on trouve souvent dans une exportation :

ticket_id	customer_id	customer_email
T-30002	C-0022	hugo.garcia@example.com
T-30111	C-0022	hugo.garcia@example.com
T-30119	C-0022	hugo.g@example.com

Hugo a changé son courriel, et une seule ligne a reçu le changement. Quelle adresse est correcte? La table ne peut pas le dire. La normalisation est la règle de conception qui empêche cela : chaque fait est stocké à un seul endroit. Les autres lignes pointent vers lui par une clé. Dans le service de soutien, le courriel est seulement dans customers, et un billet contient seulement C-0022. Le diagramme entité-relation de la dernière leçon est déjà normalisé.

Parfois, une copie vaut son coût. L’API obtient une liste de billets dans Requêtes sûres. Elle montre combien de messages chaque billet contient et quand le dernier est arrivé. Pour les compter à chaque ligne de chaque page, la base doit relire les messages encore et encore. La migration 006 garde donc une copie des deux valeurs dans le billet. C’est une dénormalisation volontaire : une copie faite à dessein, avec une raison écrite.

migrations/006_ticket_message_counts.sql (first part)

-- Deliberate denormalization: the ticket list shows how many messages a ticket
-- has and when the last one came. Counting them for every row of every list is
-- slow, so the ticket keeps both numbers. The cost: every new message must
-- update them in the same transaction (ticket_api/repository.py does).
ALTER TABLE tickets
    ADD COLUMN message_count integer NOT NULL DEFAULT 0,
    ADD COLUMN last_message_at timestamptz,
    ADD CONSTRAINT tickets_message_count_not_negative CHECK (message_count >= 0);

Le reste de 006 remplit les deux colonnes à partir des messages. Le commentaire nomme le coût; ticket_api/repository.py, qu’il mentionne, arrive au module 3. Une contrainte ne peut pas vérifier que message_count égale le nombre réel, car un CHECK voit une seule ligne. Le programme doit mettre à jour la copie chaque fois qu’il ajoute un message, et un test vérifie leur concordance. Transactions montre ce qui arrive quand le programme oublie, ou quand deux requêtes arrivent en même temps.

Exercice guidé : donner ses règles à la base de données

1. Créez la première version de migrations/005_integrity_constraints.sql avec les trois lignes ci-dessus (le ALTER TABLE messages avec sa clé étrangère). Exécutez-la :

   python -m ticket_api.migrate

   La migration s’arrête avec ForeignKeyViolation (violation de clé étrangère) et Key (ticket_id)=(T-29994) is not present in table "tickets". (la clé T-29994 est absente de la table tickets). Vérifiez que rien n’a été appliqué :

   python -m ticket_api.migrate --status

   La dernière ligne est pending 005_integrity_constraints.

2. Remplacez votre version par la vraie migration 005, et copiez 006, à partir de l’instantané :

   cp ../nextia-learning-labs/C20/snapshots/end-of-m02/migrations/00{5,6}_*.sql migrations/

   Sous Windows, dans PowerShell : Copy-Item ..\nextia-learning-labs\C20\snapshots\end-of-m02\migrations\005_*.sql, ..\nextia-learning-labs\C20\snapshots\end-of-m02\migrations\006_*.sql migrations\.

   Ouvrez 005_integrity_constraints.sql et lisez-le. Il contient huit parties numérotées, une par table, chacune avec un commentaire.

3. Appliquez les deux migrations :

   python -m ticket_api.migrate

   applied  005_integrity_constraints
   applied  006_ticket_message_counts
   Database is up to date (6 migrations).

4. Ouvrez psql et envoyez les mêmes erreurs qu’avant, cette fois sans BEGIN. Chaque instruction échoue maintenant séparément :

   INSERT INTO customers (customer_id, name, email, segment, joined_on)
   VALUES ('C-0041', 'Olga Olsen', 'Olga.Olsen@Example.com', 'home', '2026-10-09');
   INSERT INTO messages (ticket_id, author, body) VALUES ('T-99999', 'agent', 'Hello');
   UPDATE tickets SET priority = 4 WHERE ticket_id = 'T-30002';
   UPDATE tickets SET status = 'closed' WHERE ticket_id = 'T-30002';
   INSERT INTO tickets (ticket_id, subject) VALUES ('30003', '');

   ERROR:  duplicate key value violates unique constraint "customers_email_unique"
   DETAIL:  Key (lower(email))=(olga.olsen@example.com) already exists.
   ERROR:  insert or update on table "messages" violates foreign key constraint "messages_ticket_fk"
   DETAIL:  Key (ticket_id)=(T-99999) is not present in table "tickets".
   ERROR:  new row for relation "tickets" violates check constraint "tickets_priority_range"
   DETAIL:  Failing row contains (T-30002, C-0022, Charged twice for order LK-182074, My card was charged two times for order LK-182074, 449.00 dollar..., email, billing, 4, open, 2026-09-25 08:20:19+00, 2026-09-25 19:59:19+00, null, 1, 2026-09-25 19:59:19+00).
   ERROR:  new row for relation "tickets" violates check constraint "tickets_closed_at_matches_status"
   DETAIL:  Failing row contains (T-30002, C-0022, Charged twice for order LK-182074, My card was charged two times for order LK-182074, 449.00 dollar..., email, billing, 1, closed, 2026-09-25 08:20:19+00, 2026-09-25 19:59:19+00, null, 1, 2026-09-25 19:59:19+00).
   ERROR:  null value in column "customer_id" of relation "tickets" violates not-null constraint
   DETAIL:  Failing row contains (30003, null, , null, null, null, null, open, 2026-10-10 04:27:28.864201+00, 2026-10-10 04:27:28.864201+00, null, 0, null).

   Cinq écritures, cinq refus, chacun avec le nom de sa règle. La dernière ligne montre les valeurs par défaut : status est devenu open, et les deux heures sont devenues now() (vos heures diffèrent). La règle NOT NULL sur customer_id l’a arrêtée en premier. Le sujet vide et le format de 30003 n’ont donc jamais été vérifiés.

5. Essayez de supprimer T-30034, le billet avec deux photos :

   DELETE FROM tickets WHERE ticket_id = 'T-30034';

   ERROR:  update or delete on table "tickets" violates foreign key constraint "attachments_ticket_fk" on table "attachments"
   DETAIL:  Key (ticket_id)=(T-30034) is still referenced from table "attachments".

   Les fichiers protègent leur billet. Regardez les orphelins conservés avec SELECT ticket_id, author, body FROM orphaned_messages; : trois lignes, T-29994, T-29997 et T-29999. Quittez avec \q.

6. Copiez la vérification de fin du module, le fichier de tests tests/test_schema.py, et exécutez les tests :

   cp ../nextia-learning-labs/C20/snapshots/end-of-m02/tests/test_schema.py tests/
   python -m pytest

   Sous Windows : Copy-Item ..\nextia-learning-labs\C20\snapshots\end-of-m02\tests\test_schema.py tests\. Les tests affichent 49 passed (49 tests réussis). Ils utilisent leur propre base de données, tickets_test, avec les mêmes migrations.

7. Chargez à nouveau les données pour voir ce que le chargeur fait maintenant des orphelins :

   python -m scripts.load --reset

   Les décomptes se terminent par messages 331 (331 messages) et 3 messages have no ticket: they are in orphaned_messages. (3 messages n’ont pas de billet : ils sont dans orphaned_messages).

tests/test_schema.py est la vérification de fin du module : la base rejette un doublon et un orphelin, sans aide de l’API. Les noms des tests indiquent ce que chacun prouve :

tests/test_schema.py (the test names)

def test_a_duplicate_email_is_rejected_whatever_its_case(conn): ...
def test_a_duplicate_customer_id_is_rejected(conn): ...
def test_an_orphan_message_is_rejected(conn): ...
def test_a_ticket_for_a_customer_who_does_not_exist_is_rejected(conn): ...
def test_impossible_values_are_rejected(conn, column, value): ...   # 5 cases
def test_a_closed_ticket_needs_a_closing_time(conn): ...
def test_the_old_orphan_messages_were_kept_apart(conn): ...
def test_deleting_a_ticket_deletes_its_messages_and_vector(conn): ...
def test_a_ticket_with_files_cannot_be_deleted(conn): ...
def test_ai_run_costs_stay_when_their_ticket_is_deleted(conn): ...
def test_message_counts_match_the_messages(conn): ...

Erreur courante

La migration s'arrête à la première ancienne ligne qui enfreint la nouvelle règle

Symptôme : python -m ticket_api.migrate s’arrête avec psycopg.errors.ForeignKeyViolation: insert or update on table "messages" violates foreign key constraint "messages_ticket_fk" (une insertion ou une mise à jour dans messages enfreint la contrainte de clé étrangère messages_ticket_fk) et Key (ticket_id)=(T-29994) is not present in table "tickets". --status affiche encore la migration comme pending (en attente).

Cause : une nouvelle contrainte doit être respectée par chaque ligne existante, pas seulement par les nouvelles lignes. Trois anciens messages pointent vers des billets qui n’existent pas. L’erreur nomme seulement le premier.

Diagnostic : trouvez toutes les lignes qui enfreignent la règle avant de l’ajouter. Pour une clé étrangère, utilisez la requête des orphelins de la dernière leçon (LEFT JOIN … WHERE t.ticket_id IS NULL) : elle renvoie T-29994, T-29997 et T-29999. Pour un CHECK, sélectionnez les lignes où la condition est fausse.

Correction : décidez ce qui arrive à ces lignes, dans la même migration, avant la règle. Ne supprimez pas de données sur une supposition : 005 déplace les orphelins dans orphaned_messages, pour qu’une personne les vérifie. Comme une migration forme une transaction, une tentative ratée ne laisse rien derrière. Vous pouvez donc corriger le fichier et l’exécuter à nouveau.

Variation autonome : votre propre règle

Grace veut que chaque adresse courriel contienne un @. Écrivez la règle avec un CHECK (contrainte de vérification) nommé customers_email_has_at. Testez-la sans modifier votre projet : placez-la entre BEGIN; (début de transaction) et ROLLBACK; (annulation), avec un UPDATE (mise à jour) qui l’enfreint. Répondez ensuite : un CHECK pourrait-il aussi exiger qu’un message ne soit jamais plus ancien que son billet?

Afficher un exemple de réponse

BEGIN;
ALTER TABLE customers ADD CONSTRAINT customers_email_has_at CHECK (email LIKE '%_@_%');
UPDATE customers SET email = 'olga.olsen.example.com' WHERE customer_id = 'C-0001';
ROLLBACK;

BEGIN
ALTER TABLE
ERROR:  new row for relation "customers" violates check constraint "customers_email_has_at"
DETAIL:  Failing row contains (C-0001, Olga Olsen, olga.olsen.example.com, home, north, 2024-10-18, 2024-10-18 09:42:49+00).
ROLLBACK

'%_@_%' signifie : au moins un caractère, un @, au moins un caractère. Après ROLLBACK (annulation), la règle disparaît.

La deuxième règle ne peut pas être un CHECK, car un CHECK voit seulement la ligne écrite. Si vous essayez avec une sous-requête, PostgreSQL refuse : ERROR: cannot use subquery in check constraint (impossible d’utiliser une sous-requête dans une contrainte de vérification). Une règle qui porte sur deux tables nécessite le programme, un déclencheur ou une requête de vérification régulière. Ici, la requête ne trouve aucun message de ce type aujourd’hui.

Quand utiliser les contraintes et ce qu’elles coûtent

Placez une règle dans la base de données si tous les programmes doivent la respecter, maintenant et plus tard : clés, valeurs obligatoires, valeurs connues et liens entre tables. Gardez dans le programme les règles qui dépendent du contexte, comme « un remboursement de plus de 200 dollars nécessite un superviseur ».

Les contraintes ont aussi un coût :

• Chaque écriture est vérifiée. Une clé étrangère consulte l’autre table à chaque insertion. Pour les écritures du service de soutien, ce coût est faible.
• Les suppressions vérifient les lignes enfants. Supprimer un billet amène PostgreSQL à chercher ses messages, ses exécutions et ses fichiers. Sans index sur ces colonnes ticket_id, chaque recherche parcourt toute la table. Indexer de façon réfléchie mesure ce coût.
• Ajouter une règle à une grande table prend du temps, car chaque ancienne ligne est vérifiée. Migrations mesure la migration 005 sur 300 000 billets.
• Une règle rend les anciennes erreurs visibles, comme les trois lignes orphelines. C’est le but, mais prévoyez du temps pour décider quoi en faire.

De bout en boutConcevoir et optimiser le stockage d'un jeu de données public ouvertÉtude de cas

L’étude de cas Boston 311 compte les lignes qui enfreignent chaque règle avant d’ajouter une contrainte. Une règle est enfreinte par 464 demandes réelles.

Vérification des connaissances

Vérifiez votre compréhension

Un collègue ajoute UNIQUE (email) à customers, plutôt que l'index unique sur lower(email). Laquelle des insertions d'aujourd'hui laisserait-il passer, et pourquoi?

Afficher la réponse

Il laisse passer la deuxième Olga Olsen avec Olga.Olsen@Example.com, car les majuscules distinguent ce texte de olga.olsen@example.com. En pratique, les adresses courriel sont identiques, peu importe la casse. Le service de soutien aurait donc deux comptes pour une personne. L’index sur lower(email) compare les formes en minuscules et refuse l’insertion avec Key (lower(email))=(olga.olsen@example.com) already exists. (la clé existe déjà).

Vérifiez votre compréhension

Pourquoi la clé étrangère de attachments vers tickets n'a-t-elle pas de clause ON DELETE CASCADE, contrairement à celle de messages?

Afficher la réponse

Un message existe seulement dans la base de données. Le supprimer avec son billet ne laisse donc rien derrière. Une ligne de pièce jointe décrit un fichier dans le stockage objet, et la base de données ne peut pas supprimer ce fichier. Avec CASCADE, la ligne disparaîtrait, mais le fichier resterait, et personne ne saurait qu’il existe. La base de données refuse donc la suppression, et le programme doit d’abord supprimer les fichiers.

Vérifiez votre compréhension

message_count copie une information déjà présente dans la table messages. Donnez la raison de cette copie, son risque et la façon dont le projet la vérifie.

Afficher la réponse

La raison est la vitesse : la liste des billets affiche le nombre de messages pour chaque ligne. Recompter les messages pour chaque page est lent. Le risque est que la copie et les messages ne concordent pas. Cela arrive si un programme ajoute un message sans mettre le compte à jour. Un CHECK ne peut pas comparer avec une autre table. Le test test_message_counts_match_the_messages vérifie donc que le compte de chaque billet égale son nombre de messages.

Récapitulatif

• Une contrainte est une règle que la base de données vérifie à chaque écriture, pour chaque programme. Elle refuse une écriture qui l’enfreint, n’enregistre rien de cette écriture et nomme la règle dans l’erreur.
• La migration 005 ajoute NOT NULL (valeur nulle interdite), des contraintes CHECK (formats, valeurs connues, priorité de 1 à 3, heure de fermeture présente exactement quand le billet est fermé), un index unique sur lower(email) et des clés étrangères.
• Une nouvelle règle doit aussi être respectée par les anciennes lignes. La première version s’est arrêtée à la ligne orpheline T-29994; la véritable migration 005 garde les trois lignes orphelines dans orphaned_messages.
• Chaque clé étrangère précise l’effet d’une suppression : CASCADE pour les messages et les vecteurs, et SET NULL pour les exécutions d’IA. Les pièces jointes bloquent la suppression, car leurs fichiers existent à l’extérieur de la base de données.
• La normalisation garde chaque fait à un seul endroit. La migration 006 crée une copie délibérée, message_count, et le programme doit la garder exacte.
• tests/test_schema.py réussit : 49 passed (49 tests réussis). Ensuite, deux changements qui doivent se produire ensemble, et deux requêtes au même moment.

Leçon suivante : Transactions