Иллюстрированный самоучитель по SQL для начинающих

Защита данных

После первого из двух последних операторов UPDATE реализация проверяет все ограничения, которые ссылаются на измененные им значения. В число этих ограничений входит то, которое определено в таблице DEPT, потому что оно относится к столбцу Salary таблицы EMPLOYEE, а значения в этом столбце изменяются оператором UPDATE. После выполнения первого оператора UPDATE это ограничение оказалось нарушенным. Допустим, что перед выполнением этого оператора база данных находится в полном порядке и каждое значение Payroll в таблице DEPT равно сумме значений Salary из соответствующих строк таблицы EMPLOYEE. Тогда, как только первый оператор UPDATE увеличит значение Salary, это равенство выполняться не будет. Такая ситуация исправляется вторым оператором UPDATE, после выполнения которого значения базы данных соответствуют имеющимся ограничениям. Но в промежутке между этими обновлениями ограничения не выполняются.

Оператор SET CONSTRAINTS DEFERRED дает возможность временно отключить все или только указанные вами ограничения. Действие этих ограничений будет задержано до тех пор, пока выполнится или оператор SET CONSTRAINTS IMMEDIATE, или один из двух операторов: COMMIT и ROLLBACK. Следовательно, в нашем случае перед оператором UPDATE и после него необходимо поместить операторы SET CONSTRAINTS:

SET CONSTRAINTS DEFERRED;
UPDATE EMPLOYEE
SET Salary = Salary + 100
WHERE EmpNo = '123';
UPDATE DEPT D
SET Payroll = Payroll + 100
WHERE D.DeptNo = (SELECT E.DeptNo
FROM EMPLOYEE E
WHERE E.EmpNo = '123');
SET CONSTRAINTS IMMEDIATE;

Эта процедура откладывает действие всех ограничений. Например, при вставке в DEPT новых строк первичные ключи проверяться не будут; т.е. вы удалили и ту защиту, которая, возможно, вам нужна. Поэтому следует явно указывать ограничения, которые надо задержать. Для этого при создании ограничений им следует давать имена:

CREATE TABLE DEPT
(DeptNo CHAR(5),
DeptName CHAR(20),
Payroll DECIMAL(15.2),
CONSTRAINT PayEqSumsal
CHECK (Payroll = SELECT SUM(Salary)
FROM EMPLOYEE E
WHERE E.DeptNo = DEPT.DeptNo));

На ограничения с именами можно ссылаться индивидуально:

SET CONSTRAINTS PayEqSumsal DEFERRED;
UPDATE EMPLOYEE
SET Salary = Salary + 100
WHERE EmpNo = '12 3';
UPDATE DEPT D
SET Payroll = Payroll + 100
WHERE D.DeptNo = (SELECT E.DeptNo
FROM EMPLOYEE E
WHERE E.EmpNo = 423');
SET CONSTRAINTS PayEqSumsal IMMEDIATE;

Если для ограничения в операторе CREATE не указано имя, то SQL создает это имя неявно. Оно находится в специальных таблицах – таблицах каталога. Но все-таки лучше явно задавать имена ограничений.

Теперь предположим, что во втором операторе UPDATE в качестве значения возрастания вы по ошибке указали 1000. Это значение в операторе UPDATE является разрешенным, потому что имеющееся ограничение было отсрочено. Но при выполнении оператора SET CONSTRAINTS…IMMEDIATE будут проверяться указанные в нем ограничения. Если они не соблюдаются, то будет сгенерировано исключение. Но если вместо оператора SET CONSTRAINTS…IMMEDIATE выполняется оператор COMMIT, а ограничения не соблюдаются, то вместо оператора COMMIT выполняется оператор ROLLBACK.

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