Не DEFERRABLE против DEFERRABLE первоначально немедленно

Я читал это о ключевом слове SQL DEFERRABLE на Системы Баз Данных - Книги.

последний [НЕ ПОЧТИТЕЛЬНО] по умолчанию и означает, что каждый раз, когда выполняется оператор модификации базы данных, ограничение проверяется сразу же после этого, если изменение может нарушить ограничение внешнего ключа.

однако, если мы объявим ограничение отложенный, тогда у нас есть возможность чтобы он ждал, пока транзакция не будет завершена, прежде чем проверять ограничение.

мы следуем за ключевым словом отложенный или ПЕРВОНАЧАЛЬНО ОТЛОЖЕНО или ПЕРВОНАЧАЛЬНО НЕМЕДЛЕННО. В первом случае проверка будет отложена до момента фиксации каждой транзакции. В последнем случае проверка будет производиться сразу после каждого заявления.

как NOT DEFERRABLE отличается от DEFERRABLE INITIALLY IMMEDIATE? В обоих случаях кажется, любые ограничения проверяются после каждого отдельного утверждения.

4 65
sql database

4 ответа:

С DEFERRABLE INITIALLY IMMEDIATE вы можете отложить ограничения по требованию, когда вам это нужно.

это полезно, если вы обычно хотите проверить ограничения во время инструкции, но, например, пакетная загрузка хочет отложить проверку до времени фиксации.

синтаксис, как отложить ограничения отличается для различных СУБД, хотя.

С NOT DEFERRABLE вы никогда не сможете отложить проверку до времени фиксации.

50

помимо очевидной возможности отложить, разница на самом деле производительность. Если бы не было штрафа за производительность, то не было бы необходимости иметь возможность выбирать deferrable или нет-все ограничения были бы просто deferrable.

штраф за производительность связан с оптимизацией, которую может выполнять база данных, учитывая знание того, как данные ограничены. Например, индекс, созданный для поддержки уникального ограничения в Oracle, не может быть уникальный индекс, если ограничение является отложенным, так как должно быть разрешено временное разрешение дубликатов. Однако если ограничение не может быть отложено, то индекс может быть уникальным.

24

помимо других (правильных) ответов, необходимо указать, что:

  • С НЕ ПОЧТИТЕЛЬНО каждая строка проверяется во время вставки / обновления

  • С отложенный (в настоящее время немедленно) все строки проверяются в конце вставки / обновления

  • С отложенный (в настоящее время отложить) все строки проверяются в конце транзакция

Так это не правильно чтобы сказать, что отложенное ограничение действует как не отложенное, когда оно установлено в IMMEDIATE.

давайте подробно остановимся на этой разнице:

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;

это правильно выводит:

output

но если мы удалим отложенную изначально непосредственную инструкцию,

ошибка: повторяющееся значение ключа нарушает уникальная ограничение "example_row_col_key" DETAIL: Key ("row", col)=(2, 2) уже существует. ********** Ошибка **********

ошибка: повторяющееся значение ключа нарушает ограничение уникальности "example_row_col_key" состояние SQL: 23505 Detail: Key ("row", col)=(2, 2) уже существует.

дополнительное соглашение(12 октября 2017)

это поведение действительно задокументировано здесь раздел "Совместимость":

кроме того, PostgreSQL немедленно проверяет не подлежащие деферрации ограничения уникальности, а не в конце инструкции, как это предлагает стандарт.

18

не отложенный - нельзя изменить проверку ограничений, оракул проверяет его после каждой инструкции(т. е. непосредственно после оператора Insert).

DEFERRABLE первоначально немедленно-oracle проверяет ограничение после каждого оператора. Но, вы можете изменить его на после каждой транзакции (т. е. после фиксации):

set constraint pk_tab1 deferred;
2