Ограничения целостности. Представления данных

Добавление ограничения с отказом от проверки соответствия имеющимся данным

Ограничения типа "внешний ключ" и "проверка значений" (FOREIGN KEY и CHECK) могут добавляться к существующей таблице, даже если данные в таблице им противоречат. Отказаться от проверки данных при добавлении ограничения можно с помощью слова NOVALIDATE. В этом случае ограничение вступит в силу, однако не исключается, что ранее занесенные данные будут его нарушать. Автоматическая проверка ограничения, как и полагается, будет распространяться на все последующие попытки изменения данных в таблице:

SQL> CREATE TABLE deptc AS SELECT * FROM dept;
Table created.
SQL> INSERT INTO deptc ( deptno ) VALUES ( 31 );
1 row created.
SQL> ALTER TABLE deptc ADD CHECK ( deptno <= 30 );
ALTER TABLE deptc ADD CHECK ( deptno <= 30 )
*
ERROR at line 1:
ORA-02293: cannot validate (SCOTT.SYS_C005484) - check constraint violated
SQL> ALTER TABLE deptc ADD CHECK ( deptno <= 30 ) NOVALIDATE;
Table altered.
SQL> INSERT INTO deptc ( deptno ) VALUES ( 30 );
1 row created.
SQL> INSERT INTO deptc ( deptno ) VALUES ( 32 );
INSERT INTO deptc ( deptno ) VALUES ( 33 )
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C005484) violated
SQL> SELECT * FROM deptc WHERE deptno > 30;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON
        31

То есть ограничение на изменение данных действует, но в таблице встречаются его нарушения (возникшие ранее).

Такая возможность добавить ограничение без проверки соответствия ему имеющихся данных позволяет:

• сократить время в случае большой таблицы, когда у программиста есть уверенность, что имеющиеся данные ограничению не противоречат;
• включить ограничение немедленно для последующих операций с данными, отложив проверку ранее накопленых данных на потом.

Приостановка проверки заявляемых ограничений в пределах транзакции

В соответствии со стандартом SQL ANSI/ISO, с версии Oracle 8.1 именованные заявляемые ограничения целостности в Oracle можно определять со словом DEFERRABLE, например:

ALTER TABLE dept ADD CONSTRAINT u_names UNIQUE ( loc ) DEFERRABLE;

В этом случае станет возможным отложить автоматическую проверку ограничения на период до завершения текущей транзакции (подобная степень долговечности отмены проверки оправдывает употребление термина "приостановка"). С этой целью в сеансе связи с СУБД следует выдать:

SET CONSTRAINT u_names DEFERRED;

Теперь можно внести изменение, нарушающее ограничение:

INSERT INTO dept ( loc, deptno ) VALUES ( 'BOSTON', 50 );

Если дубликат не будет устранен, то сообщение об ошибке появится только при попытке выполнить COMMIT или же явочным путем возобновить конкретную проверку:

SET CONSTRAINT u_names IMMEDIATE;

Завершение транзакции (любым образом, COMMIT/ROLLBACK) отменяет все подобные сделанные приостановки. Если выдается COMMIT и есть нарушения данными хотя бы одного любого ограничения, Oracle молча подменит COMMIT на ROLLBACK. Для программиста это важное обстоятельство, так как произойдет отказ от всех изменений в пределах последней транзакции, без разбору. Если же выдается SET CONSTRAINT … IMMEDIATE, транзакция не закроется, а программа получит в ответ сообщение об ошибке.

Упражнение. Проверьте работу отложенных проверок заявляемых ограничений целостности на примере имеющегося ограничения внешнего ключа.

Вместо приостановки/возобновления проверки нескольких ограничений по отдельности можно выполнить эти действия для всех ограничений зараз, например:

SET CONSTRAINT ALL DEFERRED;

Ограничения, объявленные как DEFERRABLE, можно дополнить правилом проверки по умолчанию, например:

CREATE TABLE x (
     a NUMBER CHECK ( a > 0 ) DEFERRABLE INITIALLY DEFERRED,
     b NUMBER CHECK ( b > 1 ) DEFERRABLE INITIALLY IMMEDIATE);

Ограничение для столбца A нормально не проверяется (умолчательно приостановлено) и временно допускает вступление в силу. Ограничение для столбца B нормально проверяется и временно допускает приостановку действия. Само указание слов INITIALLY IMMEDIATE является умолчательным.

Упражнение. Проверьте срабатывание указанных для столбцов таблицы X условий на заносимые значения.

Приостановка проверки ограничений может использоваться:

• ради удобства внесения некоторых видов изменений (например, при замене в таблице значения первичного ключа или же уникальной группы при наличии ссылок внешними ключами, как компенсация за отсутствие в Oracle определения UPDATE CASCADE);
• по необходимости (например, при наложении ограничений целостности на автоматически обновляемые materialized views).

Не все специалисты в реляционном подходе к проектированию БД разделяют мнение о целесообразности приостановки проверки ограничений, принятой в SQL, указывая на другие способы решения возникающих проблем. Нельзя не заметить, что во время приостановки действия ограничений БД может давать на запросы ответы, неправильные с точки зрения заложенной в нее модели предметной области. В любом случае подобная приостановка должна использоваться программистом исключительно как временная техническая мера.

"Долговременное" отключение ограничений целостности

Действующее в схеме объявляемое ограничение целостности можно отключить также на период, более долгий, чем транзакция, вообще безотносительно к последующим транзакциям:

ALTER TABLE dept MODIFY CONSTRAINT u_names DISABLE;

Мотивами для "долговременного" отключения проверки ограничений могут, например, стать:

• погоня за скоростью загрузки данных, когда заранее известно, что данные удовлетворяют всем ограничениям;
• сохранение доступности БД при поступлении отдельных нарушающих ограничения данных (например, оказалось, что паспорта с одним номером выданы двум разным лицам).

Допускается задавать отключенность ограничения изначально при его создании.

Включение ограничения аналогично отключению, но с указанием слова ENABLE. Как и при включении проверки после ее приостановки, здесь тоже возможны конфликты с данными, однако способы разрешения конфликтов — свои собственные.

Технология включения и выключения заявляемых ограничений целостности

Если нормальный перевод ограничения в состояние ENABLED невозможен из-за накопленных противоречащих ограничению данных, Oracle предлагает два практических выхода из подобной ситуации:

• включить ограничение без проверки накопленных данных;
• выявление записей, нарушающих ограничение.

Оба решения представляют первоочередную ценность для больших таблиц.

Включение ограничения без выполнения проверки имеющихся данных

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

CREATE TABLE t (
     c VARCHAR2 ( 1 ) CONSTRAINT ctest CHECK ( c IN ( 'a', 'b' ) ) DISABLE 
);
INSERT INTO t VALUES ( 'd' );
ALTER TABLE t MODIFY CONSTRAINT ctest ENABLE; 
-- Ошибка !
ALTER TABLE t MODIFY CONSTRAINT ctest ENABLE NOVALIDATE;
INSERT INTO t VALUES ( 'd' ) ; 
-- Ошибка !
Аналогично можно указывать NOVALIDATE или VALIDATE и при отключении проверки ограничения:
ALTER TABLE t MODIFY CONSTRAINT ctest DISABLE VALIDATE; 
-- Ошибка !
ALTER TABLE t MODIFY CONSTRAINT ctest DISABLE NOVALIDATE;

Выявление записей, нарушающих ограничение

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

1. нужно иметь специальную таблицу для хранения адресов строк. Типовой сценарий создания такой таблицы есть в %ORACLE_HOME%\rdbms\admin\utlexcpt.sql (для обычных таблиц) и в utlexpt1.sql (для индексно организованных таблиц). Имя таблицы, которую заводит сценарий utlexcpt.sql, — EXCEPTIONS. Подобных таблиц можно завести несколько;
2. выдать команду типа

   ALTER TABLE t MODIFY CONSTRAINT ctest ENABLE EXCEPTIONS INTO exceptions;
   

Если нарушения ограничения CTEST данными обнаружится, в программу вернется ошибка, но при этом в указанную в команде таблицу EXCEPTIONS СУБД занесет список физических адресов строк, нарушающих ограничение. После корректировки данных в таблице T соответствующие ей строки в EXCEPTIONS нужно не забыть удалить самостоятельно, так как Oracle, разумеется, этого не сделает.