sql - que - NO DEFERRABLE versus DEFERRABLE INMEDIATAMENTE INMEDIATO

sql database for beginners

Question

Leí esto sobre la palabra clave SQL DEFERRABLE en los sistemas de bases de datos: el libro completo .

El último [NO DEFERSABLE] es el predeterminado, y significa que cada vez que se ejecuta una declaración de modificación de base de datos, la restricción se verifica inmediatamente después, si la modificación puede violar la restricción de clave externa.

Sin embargo, si declaramos que una restricción es DEFERRABLE , entonces tenemos la opción de que espere hasta que se complete una transacción antes de verificar la restricción.

Seguimos la palabra clave DEFERRABLE ya sea INICIALMENTE DIFERIDA o INMEDIATAMENTE INMEDIATA . En el primer caso, la verificación se aplazará justo antes de que se comprometa cada transacción. En este último caso, el cheque se realizará inmediatamente después de cada declaración.

¿Cómo NOT DEFERRABLE diferente de DEFERRABLE INITIALLY IMMEDIATE ? En ambos casos, parece que cualquier restricción se verifica después de cada declaración individual.

Answer 1

Aparte de las otras respuestas (correctas), se debe indicar que:

• con NOT DEFERRABLE cada fila se verifica en el momento de insertar / actualizar

• con DEFERRABLE (actualmente INMEDIATO ) todas las filas se verifican al final de la inserción / actualización

• con DEFERRABLE (actualmente DIFERIDO ) todas las filas se verifican al final de la transacción

Por lo tanto , no es correcto decir que una restricción DEFERRABLE actúa como NO DEF ERRABLE cuando está configurada como INMEDIATA.

Vamos a elaborar sobre esta diferencia:

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;

Esto produce correctamente:

Pero si eliminamos la instrucción DEFERRABLE INICIALMENTE INMEDIATA,

ERROR: el valor duplicado de la clave viola la restricción única "example_row_col_key" DETAIL: Key ("row", col) = (2, 2) ya existe. ********** Error **********

ERROR: el valor duplicado de la clave viola la restricción única "example_row_col_key" Estado SQL: 23505 Detalle: La clave ("fila", col) = (2, 2) ya existe.

ADDENDUM (12 de octubre de 2017)

Este comportamiento está de hecho documentado here , sección "Compatibilidad":

Además, PostgreSQL verifica las restricciones de exclusividad no diferibles inmediatamente, no al final de la declaración, como sugeriría la norma.

Answer 2

Aparte de lo obvio de poder posponer, la diferencia es en realidad el rendimiento. Si no hubiera una penalización por desempeño, entonces no habría necesidad de tener una opción para elegir diferible o no, todas las restricciones serían simplemente diferibles.

La penalización del rendimiento tiene que ver con las optimizaciones que puede realizar la base de datos dado el conocimiento de cómo se restringen los datos. Por ejemplo, el índice que se crea para respaldar una restricción única en Oracle no puede ser un índice único si la restricción es diferible, ya que se deben permitir temporalmente los duplicados. Sin embargo, si la restricción no es diferible, entonces el índice puede ser único.

Answer 3

Con DEFERRABLE INITIALLY IMMEDIATE , puede aplazar las restricciones de la demanda cuando la necesite.

Esto es útil si normalmente desea verificar las restricciones en el momento de la declaración, pero por ejemplo, una carga por lotes desea diferir la verificación hasta el momento de la confirmación.

Sin embargo, la sintaxis de cómo diferir las restricciones es diferente para los diversos DBMS.

Con NOT DEFERRABLE nunca podrá diferir la verificación hasta el momento del compromiso.

Answer 4

NO DEF ERRABLE - no puede cambiar la comprobación de restricciones, Oracle la verifica después de cada declaración (es decir, directamente después de la instrucción de inserción).

DEFERRABLE INICIALMENTE INMEDIATO - Oracle verifica la restricción después de cada declaración. PERO, puede cambiarlo después de cada transacción (es decir, después de la confirmación):

set constraint pk_tab1 deferred;