Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 7046 / 737 | Оценка: 3.81 / 3.53 | Длительность: 19:30:00
ISBN: 978-5-9963-0488-2
Лекция 10:

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

< Лекция 9 || Лекция 10: 12345 || Лекция 11 >

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

Ограничения типа "внешний ключ" и "проверка значений" (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, разумеется, этого не сделает.

< Лекция 9 || Лекция 10: 12345 || Лекция 11 >
Ярослав Прозоров
Ярослав Прозоров

В лекции № 7 "Введение в Oracle SQL" в подразделе "Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS"  представленная таблица сравнения содержит ошибки - окончания запросов пропущены. Видимо, ошибки вызваны некорректным переносом материала лекции.

Володимир Миколайчук
Володимир Миколайчук
Помогите разобраться поетапно с логикой запроса
-------TOOLS
NAME PRICE TYPE
drill 155 A
sawzall 192 N
mitre saw 292 M
router 86 I
RAD 145 M
jigsaw 128 I
screwdriver 77 P
------TOOL_TYPES
TYPE USAGE
A Always
I Often
M Sometimes
N Rarely
P Never

Запрос SQL:
SELECT t.type, SUM(t.price)
FROM tools t
GROUP BY t.type
HAVING SUM(t.price) >= (SELECT AVG(price)
FROM tools
WHERE type IN (SELECT type
FROM tool_types
WHERE usage = 'Often'));

И сколько строк он все таки вернет
Анатолий Федоров
Анатолий Федоров
Россия, Москва, Московский государственный университет им. М. В. Ломоносова, 1989
Дмитрий Квашнёв
Дмитрий Квашнёв
Россия, Коломна, Московский государственный открытый университет, 2001