Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 6782 / 673 | Оценка: 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'));

И сколько строк он все таки вернет
Андрияна Ланге
Андрияна Ланге
Россия, г. Москва
Ольга Угарова
Ольга Угарова
Россия, Москва, НИЯУ МИФИ