Подтвердить что ты не робот

НЕ ОТКАЗЫВАЕТСЯ В ОТНОШЕНИИ НЕПОСРЕДСТВЕННО НЕМЕДЛЕННО

Я прочитал это о ключевом слове SQL DEFERRABLE в системах баз данных - Полная книга.

Последний [NOT DEFERRABLE] по умолчанию и означает, что каждый раз, когда выполняется инструкция модификации базы данных, ограничение сразу же проверяется, если модификация может нарушить ограничение внешнего ключа.

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

Мы выполняем ключевое слово DEFERRABLE с помощью INITIALLY DEFERRED или INITIALLY IMMEDIATE. В первом случае проверка будет отложена непосредственно перед каждой транзакцией. В последнем случае проверка будет производиться сразу после каждого заявления.

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

4b9b3361

ОТВЕТЫ

Ответ 1

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

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

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

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

Ответ 2

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

  • с NOT DEFERRABLE каждая строка проверяется во время вставки/обновления

  • с DEFERRABLE (в настоящее время IMMEDIATE) все строки проверяются в конце вставки/обновления

  • с DEFERRABLE (в настоящее время DEFERRED) все строки проверяются в конце транзакции

Поэтому неправильно говорить, что ограничение DEFERRABLE действует как NOT DEFERRABLE, когда оно установлено в 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

Но если мы удалим инструкцию DEFERRABLE INITIALLY IMMEDIATE,

ОШИБКА: значение дублированного ключа нарушает уникальное ограничение "example_row_col_key". ПОДРОБНЕЕ: Ключ ("строка", столбец) = (2, 2) уже существует. ********** Ошибка **********

ОШИБКА: значение дублированного ключа нарушает уникальное ограничение "example_row_col_key" Состояние SQL: 23505 Подробности: ключ ("строка", столбец) = (2, 2) уже существует.

ДОБАВЛЕНИЕ (12 октября 2017 г.)

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

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

Ответ 3

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

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

Ответ 4

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

НЕИСПРАВНОСТЬ НЕИСПРАВНО НЕПРАВИЛЬНО - оракул проверяет ограничение после каждого утверждения. НО, вы можете изменить его после каждой транзакции (т.е. После фиксации):

set constraint pk_tab1 deferred;

Ответ 5

Я очень опаздываю на вечеринку, но я хотел добавить, что - по состоянию на декабрь 2018 года - только две базы данных, о которых я знаю (может быть и больше), предлагают некоторый уровень реализации этой стандартной функции SQL:

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 Несмотря на то, что Oracle 12c принимает NOT DEFERRABLE ограничения NOT DEFERRABLE, он фактически игнорирует его и заставляет его работать как DEFERRABLE INITIALLY IMMEDIATE.

Как вы видите, Oracle не реализует первый тип (NOT DEFERRABLE), и поэтому разработчики, использующие Oracle (в данном случае OP), могут запутаться и считать первые два типа эквивалентными.

Интересно, что Oracle и PostgreSQL имеют разные типы по умолчанию. Может быть, это влияет на производительность.