Dans mon ouvrage sur SQL aux éditions Pearson Education, nous parlons de la notion de déférabilité des contraintes SQL (chapitre 3, paragraphe 7).

Un internaute qui a lu mon livre (oui, ça existe !) se posait la question suivante :

je n’ai pas compris la différence entre une contrainte NOT DEFERRABLE et une contrainte DEFERRABLE INITIALLY IMMEDIATE.
« Pas déférable » c’est forcement immédiat… mais « deférable immédiat » c’est quoi ? …la différence doit être subtile…

Voici la réponse que je lui ais apporté :

Le pilotage de la déferrabilité des contraintes procède en deux temps :

1) la contrainte peut être déférée ou ne peut pas l’être :

ALTER TABLE ... ADD CONSTRAINT ... NOT DEFERRABLE => n''est jamais déférable
ALTER TABLE ... ADD CONSTRAINT ... DEFERRABLE => est déferrable (mais uniquement sur demande, pas par défaut, donc INITIALY IMMEDIATE)
ALTER TABLE ... ADD CONSTRAINT ... DEFERRABLE INITIALY IMMEDIATE => (même que précédemment : est déferrable, mais uniquement sur demande, pas par défaut)
ALTER TABLE ... ADD CONSTRAINT ... DEFERRABLE INITIALY DEFERRED => est déferrable et systématiquement déférée, sauf si on lui impose le contraire à l'aide du SET CONSTRAINTS...

2) déferrement de la contrainte :

SET CONSTRAINTS <...> DEFERRED  
Impose que les contraintes mentionnées dans <...> et déferrables soient déférées
SET CONSTRAINTS <...> IMMEDIATE
Impose que les contraintes mentionnées dans <...> soit immédiatement appliquées

Le déferrement n’intervient que dans une transaction et si contraintes déférée il y a, alors la vérification de la contrainte sera effectuée au moment du COMMIT et non à chaque ordre SQL.

A noter : c’est le seul endroit dans SQL ou lançant un COMMIT on peut obtenir un ROLLBACK. En effet si une seule des contraintes déférée est violée alors la transaction est annulée…

Il me demandait aussi :

Par contre je ne vois pas le rapport avec « déférer » alors que l’on parle bien de « différer » dans le temps… Encore un 2e mystère…

Sur le terme déféré : le mot provient du jargon juridique et indique un transfert de responsabilité… Le prévenu a été déféré au parquet. Signifie que la police qui détenait le malfaiteur a confié à l’autorité judiciaire la responsabilité de l’individu en question. C’est bien ce qui se passe en définitive avec SQL. L’ordre SQL INSERT, UPDATE ou DELETE, délaisse l’application des contraintes dont il doit habituellement s’occuper pour en confier la vérification à la transaction. On voit bien qu’il y a transfert de responsabilité !… et non différemment dans le temps, même si l’un induit l’autre (quoique la notion d’atomicité des transactions devrait nous faire considérer que cette durée n’a pas de consistance…).

***
Fr̩d̩ric BROUARD РSQLpro РMVP SQL Server
Spécialiste SQL/BD modélisation de données
SQL & SGBDR http://sqlpro.developpez.com/
Expert SQL Server : http://www.sqlspot.com
audits – optimisation – tuning – formation