Режим проверки ограничения ссылочной целостности

Функция

Определение режима проверки ограничения целостности (немедленная проверка или отложенная).

Спецификация
   
< режим проверки ограничения ссылочной целостности >::=
SET CONSTRAINTS ALL {DEFERRED | IMMEDIATE}
Синтаксические правила
  1. Модификатор IMMEDIATE устанавливает режим немедленной проверки ограничений целостности. Используется по умолчанию.

  2. Модификатор DEFERRED устанавливает режим отложенной проверки ограничений целостности.

Общие правила
  1. Модификатор DEFERRED относится только к проверке ограничений целостности. Обработка других ошибок обработки данных выполняется немедленно.

  2. DEFERRED действует только для ограничений целостности с признаком NO ACTION (для режимов CASCADE, SET DEFAULT, SET NULL не действует).

  3. Режим SET CONSTRAINTS ALL DEFERRED:

    применяется к проверке ограничений целостности CHECK и FOREIGN KEY и не применяется для проверки ограничений целостности:

    • PRIMARY KEY (на NOT NULL-значение и отсутствие дубликатов);

    • UNIQUE (на отсутствие дубликатов);

    • NOT NULL-значений.

  4. Модификатор DEFERRED устанавливает режим отложенной проверки ограничений целостности – до окончания текущей транзакции (только по COMMIT) или до установки режима IMMEDIATE.

  5. Режим IMMEDIATE может быть установлен в любой момент обработки данных.

  6. Если задан режим DEFERRED, и транзакция завершается пользователем по COMMIT, но в процессе выполнения COMMIT выявляется нарушение целостности, такая транзакция откатывается (автоматически выполняется ROLLBACK).

  7. Подтверждение или откат транзакции (COMMIT/ROLLBACK) устанавливает режим IMMEDIATE.

Пример
Для составных индексов (sql-скрипт).
Общая для всех часть:
drop table sec;
drop table prim;
create table prim( i1 int, ch char(1), j1 int, primary key(i1,j1) );

create table sec( i2 int, ch char(3), j2 int );
alter table sec add FOREIGN KEY (i2,j2) references prim( i1, j1 );
exclusive;  // это команда inl-интерфейса

SET CONSTRAINTS ALL DEFERRED;
1) добавление значения в таблицу с ссылочным ключом, когда запись отсутствует в
 основной таблице:
exclusive; // это команда inl-интерфейса
insert into sec(i2,ch,j2) values(1, 'aaa',1);
insert into prim(i1,ch,j1) values(1,'b',1);

2) изменение значения в таблице с ссылочным ключом, когда новое значение отсутствует в основной таблице:
insert into prim(i1,ch,j1) values(1,'b',1);
insert into sec(i2,ch,j2) values(1,'aaa',1);

update sec set j2 = 2, i2 = 3;
update prim set j1 = 2, i1 = 3;

3) удаление значения в основной таблице, когда значение присутствует в таблице с ссылочным ключом:
insert into prim(i1,ch,j1) values(1,'b',1);
insert into sec(i2,ch,j2) values(1,'aaa',1);

delete from prim where i1=1 and j1=1;
delete from sec where i2=1 and j2=1;

4) изменение значения в основной таблице, когда значение присутствует в таблице с ссылочным ключом:
insert into prim(i1,ch,j1) values(1,'b',1);
insert into sec(i2,ch,j2) values(1,'aaa',1);

update prim set i1=2, j1=3;
update sec set i2=2, j2=3;

Общая для всех часть:
commit;
select * from prim;
select * from sec;