NOT DEFERRABLE versus DEFERRABLE INITIALEMENT IMMEDIATE

Question

J'ai lu ceci à propos du mot-clé SQL DEFERRABLE en Systèmes de bases de données - Le livre complet .

Ce dernier [NON REPORTABLE] est la valeur par défaut, et signifie que chaque fois qu'une instruction de modification de la base de données est exécutée, la contrainte est vérifiée immédiatement après, si la modification peut violer la contrainte de clé étrangère.

Cependant, si nous déclarons qu'une contrainte est DÉFERRABLE nous avons la possibilité d'attendre la fin d'une transaction avant de vérifier la contrainte.

Nous suivons le mot-clé DÉFERRABLE soit par INITIALEMENT REPORTÉ o DANS UN PREMIER TEMPS IMMÉDIAT . Dans le premier cas, la vérification sera reportée juste avant que chaque transaction ne soit validée. Dans le second cas, le contrôle sera effectué immédiatement après chaque déclaration.

Comment la NOT DEFERRABLE différent de DEFERRABLE INITIALLY IMMEDIATE ? Dans les deux cas, il semble que les contraintes soient vérifiées après chaque déclaration individuelle.

Answer 1

Avec DEFERRABLE INITIALLY IMMEDIATE vous pouvez reporter les contraintes sur demande lorsque vous en avez besoin.

Ceci est utile si vous souhaitez normalement vérifier les contraintes au moment de la déclaration, mais que pour un chargement par lots, par exemple, vous souhaitez différer la vérification jusqu'au moment de la validation.

La syntaxe de report des contraintes est cependant différente selon les SGBD.

Avec NOT DEFERRABLE vous ne pourrez jamais reporter le contrôle au moment de l'engagement.

Answer 2

En dehors des autres réponses (correctes), lorsqu'il s'agit de PostgreSQL il convient de préciser que :

• avec PAS DEFERRABLE chaque ligne est vérifiée au moment de l'insertion ou de la mise à jour

• avec DÉFERRABLE (actuellement IMMEDIATE ) toutes les lignes sont vérifiées à la fin de l'insertion/mise à jour

• avec DÉFERRABLE (actuellement DÉFENSE ) toutes les lignes sont vérifiées à la fin de la transaction

Donc ce n'est pas correct pour dire qu'une contrainte DEFERRABLE réglée sur IMMEDIATE agit comme une contrainte NOT DEFERRABLE.

---

Détaillons cette différence :

CREATE TABLE example(
    row integer NOT NULL,
    col integer NOT NULL,
    UNIQUE (row, col) DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO example (row, col) VALUES (1,1),(2,2),(3,3);

UPDATE example SET row = row + 1, col = col + 1;

SELECT * FROM example;

Le résultat est correct :

Mais si nous supprimons l'instruction DEFERRABLE INITIALLY IMMEDIATE,

ERREUR : la valeur de la clé dupliquée viole la contrainte d'unicité "exemple_rangée_clé_col" DÉTAIL : La clé ("row", col)=(2, 2) existe déjà. ********** Erreur **********

ERREUR : la valeur de la clé dupliquée viole la contrainte d'unicité "example_row_col_key" SQL state : 23505 Detail : Clé ("row", col)=(2, 2) existe déjà.

---

ADDENDUM (12 octobre 2017)

Ce comportement est en effet documenté aquí , section "Compatibilité" :

De plus, PostgreSQL vérifie les contraintes d'unicité non-déferrables immédiatement, et non pas à la fin de la déclaration comme le suggère la norme.

Answer 3

Outre l'aspect évident de la possibilité de report, la différence se situe au niveau de la performance. S'il n'y avait pas de pénalité de performance, il n'y aurait pas besoin d'avoir une option pour choisir de reporter ou non - toutes les contraintes seraient simplement reportables.

La pénalité de performance est liée aux optimisations que la base de données peut effectuer en sachant comment les données sont restreintes. Par exemple, l'index créé pour soutenir une contrainte unique dans Oracle ne peut pas être un index unique si la contrainte est reportable, puisqu'il faut autoriser temporairement les doublons. Toutefois, si la contrainte n'est pas reportable, l'index peut être unique.

Answer 4

Je suis très en retard sur la fête, mais je voulais ajouter que -- en décembre 2018 -- seules deux bases de données que je connais (il y en a peut-être d'autres) offrent un certain niveau de mise en œuvre de cette fonctionnalité SQL standard :

Database    NOT DEFERRABLE  DEFERRABLE           DEFERRABLE 
                            INITIALLY IMMEDIATE  INITIALLY DEFERRED
----------  --------------  -------------------  ------------------
Oracle      N/A*1           Yes (default)        Yes
PostgreSQL  Yes (default)   Yes                  Yes
DB2         -               -                    -
SQL Server  -               -                    -
MySQL       -               -                    -
MariaDB     -               -                    -
SAP Sybase  -               -                    -
HyperSQL    -               -                    -
H2          -               -                    -
Derby       -               -                    -

*1 Même si Oracle 12c accepte l'état de la contrainte NOT DEFERRABLE, il l'ignore en fait et la fait fonctionner comme DEFERRABLE INITIALLY IMMEDIATE.

Comme vous le voyez, Oracle n'implémente pas le premier type (NOT DEFERRABLE), et c'est pourquoi les développeurs utilisant Oracle (le PO dans ce cas) peuvent être confus et considérer les deux premiers types comme équivalents.

Il est intéressant de noter qu'Oracle et PostgreSQL ont un type par défaut différent. Cela a peut-être des conséquences sur les performances.

Answer 5

NOT DEFERRABLE - vous ne pouvez pas modifier la vérification des contraintes, l'oracle la vérifie après chaque déclaration (c'est-à-dire directement après la déclaration d'insertion).

DÉFERRABLE INITIALEMENT IMMÉDIATE - l'oracle vérifie la contrainte après chaque déclaration. MAIS, vous pouvez le modifier pour qu'il le fasse après chaque transaction (c.-à-d. après le commit) :

set constraint pk_tab1 deferred;