Блокирование объектов базы данных
Ограничения в транзакциях
Проверка достоверности данных заключается не только в проверке правильности типа данных. Кроме этого, возможно, потребуется следить, чтобы в некоторых столбцах не было неопределенных значений, а в других — значения не выходили за границы определенного диапазона. Об этих ограничениях см. в главе 5.
Говорить о связи ограничений с транзакциями имеет смысл потому, что первые влияют на работу последних. Предположим, например, что нужно добавить данные в таблицу, в которой имеется столбец с ограничением NOT NULL. Как правило, в начале создают пустую строку и затем заполняют ее значениями. Однако ограничение NOT NULL не позволит воспользоваться данным способом, поскольку SQL не даст ввести строку с неопределенным значением, находящимся в столбце с ограничением NOT NULL, даже если данные
в этот столбец предполагается ввести еще до конца транзакции. Для решения этой проблемы в SQL:2003 существует возможность определять ограничения как DEFERRABLE (задерживаемые) или NOT DEFERRABLE (незадерживаемые).
Ограничения, определенные как NOT DEFERRABLE, применяются немедленно. А те, что определены как DEFERRABLE, первоначально могут быть заданы как DEFERRED (задержанные) или IMMEDIATE (немедленные). Если ограничение типа DEFERRABLE задано как IMMEDIATE, то оно действует так, как и ограничение типа NOT DEFERRABLE, — немедленно. Если же ограничение типа DEFERRABLE задано как DEFERRED, то его действие может быть отсрочено.
Чтобы добавлять пустые записи или выполнять другие операции, которые могут нарушить ограничения типа DEFERRABLE, можно использовать следующий оператор:
SET CONSTRAINTS ALL DEFERRED ;
Он определяет все ограничения типа DEFERRABLE как DEFERRED. На ограничения типа NOT DEFERRABLE этот оператор не действует. После того как выполнены все операции, которые могут нарушить ваши ограничения, и таблица достигла того состояния, когда их нарушать уже нельзя, тогда эти ограничения можно применить заново. Соответствующий оператор выглядит следующим образом:
SET CONSTRAINTS ALL IMMEDIATE ;
Если вы сделали ошибку и данные не соответствуют каким-либо ограничениям, вы сразу же это увидите после выполнения данного оператора.
Если ограничения, ранее заданные как DEFERRED, явно не задаются вами как IMMEDIATE, то, когда вы попытаетесь завершить свою транзакцию с помощью оператора COMMIT, SQL активизирует все задержанные ограничения. Если к этому моменту не все ограничения выполняются, транзакция будет отменена и SQL выдаст сообщение об ошибке.
Ограничения в SQL защищают от ввода неправильных данных (или, что так же важно, от недопустимого отсутствия данных), причем у вас есть возможность на время действия транзакции временно отменить имеющиеся ограничения.
Чтобы увидеть, насколько важна отсрочка применения ограничений, проанализируйте пример с платежными ведомостями.
Предположим, что в таблице EMPLOYEE (сотрудник) имеются столбцы EmpNo (номер сотрудника), EmpName (фамилия сотрудника), DeptNo (номер отдела) и Salary (оклад). DeptNo является внешним ключом, который ссылается на таблицу DEPT (отдел). Предположим также, что в таблице DEPT имеются столбцы DeptNo и DeptName (название отдела), причем DeptNo — это первичный ключ.
Представим, что, кроме этого, вы хотите иметь такую же таблицу, как и DEPT, но в которой еще есть столбец Payroll (платежная ведомость), и в нем для каждого отдела имеется сумма значений Salary сотрудников этого отдела.
Эквивалент этой таблицы можно создать с помощью следующего представления:
CREATE VIEW DEPT2 AS
SELECT D.*, SUM(E.Salary) AS Payroll
FROM DEPT D, EMPLOYEE E
WHERE D.DeptNo = E.DeptNo
GROUP BY D.DeptNo ;
Точно такое же представление можно определить еще и другим способом:
CREATE VIEW DEPT3 AS
SELECT D.*,
(SELECT SUM(E.Salary)
FROM EMPLOYEE E
WHERE D.DeptNo = E.DeptNo) AS Payroll
FROM DEPT D ;
Но предположим, что для большей эффективности вы не собираетесь вычислять значение SUM каждый раз, когда ссылаетесь на столбец DEPT.Payroll. Вместо этого вы хотите, чтобы в таблице DEPT в действительности находился столбец Payroll. Значения в этом столбце вы будете обновлять каждый раз, когда будете вносить изменения в столбце Salary.
И, чтобы обеспечить правильность значений в столбце Salary, в определение таблицы можно вставить ограничение:
CREATE TABLE DEPT
(DeptNo CHAR(5),
DeptName CHAR(20),
Payroll DECIMAL(15,2),
CHECK (Payroll = (SELECT SUM(Salary)
FROM EMPLOYEE E WHERE E.DeptNo = DEPT.DeptNo)));
Теперь предположим, что вам надо увеличить на 100 значение Salary сотрудника под номером 123. Это можно сделать с помощью следующего обновления:
UPDATE EMPLOYEE
SET Salary = Salary + 100
WHERE EmpNo = '123' ;
Кроме того, следует обновить таблицу DEPT:
UPDATE DEPT D
SET Payroll = Payroll + 100
WHERE D.DeptNo = (SELECT E.DeptNo
FROM EMPLOYEE E
WHERE E.EmpNo 423') ;
(Подзапрос используется для того, чтобы получить ссылку на значение DeptNo сотрудника с идентификационным номером 123.)
Но здесь имеется трудность: после каждого оператора ограничения проверяются. Теоретически должны проверяться все ограничения. Но на практике реализациями проверяются только те из них, которые относятся к значениям, измененным в ходе работы оператора.
После первого из двух последних операторов 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.
Подведем итог. Временно отменять действие ограничений можно только внутри транзакции. Как только транзакция завершается, ограничения сразу же вступают в силу. Если использовать эту возможность правильно, то транзакция не создаст никаких данных, нарушающих какие-либо ограничения.