Целостность данных в oracle

Обновлено: 15.01.2025

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

Первичный ключ

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

Уникальный ключ

Уникальный ключ - это столбец или группа столбцов, значения (комбинация значений для группы столбцов) которых не могут повторяться. Отличия уникального ключа от первичного - в том, что:

уникальных ключей для одной таблицы может быть несколько (вопросик на засыпку для тех, кто прочитал статью про нормализацию: правила какой нормальной формы при этом будут нарушены? ;)
уникальные ключи могут иметь значения NULL, при этом если имеется несколько строк со значениями уникального ключа NULL, такие строки согласно стандарту SQL 92 считаются различными (уникальными).

Внешний ключ

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

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

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

Ссылочная целостность

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

Более интересные моменты возникают, когда мы удаляем или изменяем строки родительской таблицы. Как при этом не допустить появления \"болтающихся в воздухе\" строк дочерней таблицы? Для этого существуют правила ссылочной целостности ON UPDATE и ON DELETE, которые, по стандарту SQL 92, могут содержать следующие инструкции:

CASCADE - обеспечивает автоматическое выполнение в дочерней таблице тех же изменений, которые были сделаны в родительском ключе. Если родительский ключ был изменен - ON UPDATE CASCADE обеспечит точно такие же изменения внешнего ключа в дочерней таблице. Если строка родительской таблицы была удалена, ON DELETE CASCADE обеспечит удаление всех соответствующих строк дочерней таблицы.
SET NULL - при удалении строки родительской таблицы ON DELETE SET NULL установит значение NULL во всех столбцах вторичного ключа в соответствующих строках дочерней таблицы. При изменении родительского ключа ON UPDATE SET NULL установит значение NULL в соответствующих столбцах соответствующих строк (о как:) дочерней таблицы.
SET DEFAULT - работает аналогично SET NULL, только записывает в соответствующие ячейки не NULL, а значения, установленные по умолчанию.
NO ACTION (установлено по умолчанию) - при изменении родительского ключа никаких действий с внешним ключом в дочерней таблице не производится. Но если изменение значений родительского ключа приводит к нарушению ссылочной целосности (т.е. к появлению "висящих в воздухе" строк дочерней таблицы), то СУБД не даст произвести такие изменения родительской таблицы.

Ну а сейчас - от общего к частному.

Ключи и ссылочная целостность в MySQL и Oracle

Oracle поддерживает первичные, уникальные, внешние ключи в полном объеме. Oracle поддерживает следующие правила ссылочной целостности:

NO ACTION (устанавливается по умолчанию) в более жестком, чем по стандарту SQL 92, варианте: запрещается изменение и удаление строк родительской таблицы, для которых имеются связанные строки в дочерних таблицах.
ON DELETE CASCADE.

Более сложные правила ссылочной целостности в Oracle можно реализовать через механизм триггеров.

MySQL версии 4.1 (последняя на момент написания статьи стабильная версия) позволяет в командах CREATE / ALTER TABLE задавать фразы REFERENCES / FOREIGN KEY, но в работе никак их не учитывает и реально внешние ключи не создает. Соответственно правила ссылочной целостности, реализуемые через внешние ключи, в MySQL не поддерживаются. И все заботы по обеспечению целостности и непротиворечивости информации в базе MySQL ложатся на плечи разработчиков клиентских приложений.

Разработчики MySQL обещают реализовать работу с внешними ключами и поддержание ссылочной целостности в версии 5.0. Что ж, когда версия MySQL 5.0 станет стабильной - посмотрим, что там в итоге получится. Очень, очень хотелось бы, чтобы MySQL поддерживала ссылочную целостность (без ущерба для производительности:).

Ограничения целостности в реляционных базах данных (и Oracle Database в частности) позволяют легко и автоматически применять важные бизнес-правила к таблицам базы данных. Например, в таблице, используемой в системе управления кадрами, нельзя завести сотрудника, не назначив ему руководителя. При создании связанных таблиц можно объявить необходимые ограничения целостности, которые должны быть удовлетворены при каждом вводе или модификации данных в таблице.

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

По умолчанию Oracle разрешает значения null во всех столбцах. Если эти значения недопустимы для некоторых столбцов таблицы, для них необходимо задавать ограничение NOT NULL. Обратите внимание, что налагать ограничения базы данных на таблицы можно как во время их создания, так и позднее, с помощью команды ALTER TABLE.Однако очевидно, что если в таблице уже имеются значения null или дублированные данные, то изменить таблицу, применив ограничение NOT NULL или ограничение уникальности, будет невозможно.

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

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

Ограничения первичного ключа

Первичный ключ — очень важный тип ограничения в таблице. Когда необходимо,чтобы значения столбца идентифицировались уникальным образом, это обеспечивается созданием первичного ключа по значению столбца. Столбец, на котором определен первичный ключ, должен быть уникальным и не null.

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

Кроме того, можно добавить ограничение и к существующей таблице:

Поскольку в предыдущем примере ограничению не присвоено имя, Oracle даст ему имя, сгенерированное системой. Если требуется присвоить ограничению собственное имя, воспользуйтесь следующей командой, которая назовет ограничение dept_pk:

Обратите внимание, что если первичный ключ будет построен по более чем одному столбцу (т.е. будет составным), специфицировать назначение первичного ключа напротив имени столбца при создании таблицы нельзя. Столбцы первичного ключа должны быть указаны как отдельный элемент оператора CREATE TABLE после списка всех столбцов.

На заметку! В обоих предыдущих примерах Oracle автоматически создает индексы на столбце,который назначен первичным ключом.

Ограничения NOT NULL

Обычно таблица имеет один или более столбцов, в которых не допускается значение null — т.е. без значений. Хорошим примером является столбец last_name таблицы employee. Заставить пользователей обязательно вносить значения в этот столбец можно при создании таблицы, указав опцию NOT NULL для столбца, который не должен быть null:

Если таблица уже создана, и требуется модифицировать столбец с допускающего null на не допускающий null, для этого можно использовать следующий оператор:

Проверочные ограничения

используете Проверочные (CHECK) ограничения применяются для обеспечения соответствия данных столбца определенным параметрам, которые вы укажете. Например,предположим, что годовая зарплата сотрудника фирмы не может быть равна или превышать $100 000 при определенных обстоятельствах. Это ограничение можно навязать с помощью следующего оператора, который устанавливает ограничение CHECK на столбце SALARY:

Ограничение уникальности

Ограничение уникальности (UNIQUE) очень распространено в реляционных базах данных. Эти ограничения гарантируют уникальность строк в реляционной таблице.В таблице могут существовать сразу несколько таких ограничений. Например, ограничение уникальности на столбце employee_id гарантирует, что ни один сотрудник не появится дважды в таблице employee.

В следующем примере первый оператор специфицирует ограничение уникальности на комбинации столбцов dept_name и location:

Создать уникальное ограничение на таблице department можно также с помощью такого оператора ALTER TABLE:

Ограничения ссылочной целостности

Ограничения ссылочной целостности гарантируют, что значения определенных важных столбцов будут иметь смысл. Предположим, что есть родительская таблица, которая ссылается на значения из другой таблицы, как в случае таблиц dept и employee.Сотрудник в таблице employee не может быть назначен в департамент, если такой департамент в таблице dept не существует.

Гарантировать существование действительного департамента можно с помощью ограничения ссылочной целостности. В этом случае столбец departament_id служит первичным ключом таблицы dept, а столбец dept_id в таблице employee, ссылающийся на соответствующий столбец таблицы department, называется внешним ключом. Таблица,содержащая внешний ключ, обычно называется дочерней таблицей, а таблица, содержащая ключ, на который ссылается внешний ключ — родительской таблицей. Как и со всеми прочими типами ограничений, ссылочные ограничения целостности можно создавать во время создания таблицы или позднее, с помощью оператора ALTER TABLE:

База данных назначает столбец dept_id таблицы employee внешним ключом, потому что он ссылается на столбец dept_id таблицы dept. Обратите внимание, что для того, чтобы столбец служил ссылочным (на который ссылаются), он должен быть уникальным или же быть первичным ключом в таблице, на которую установлена ссылка.

Состояния ограничений целостности

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

При загрузке больших объемов данных с использованием SQL*Loader или утилиты Import может потребоваться значительное время на загрузку данных, каждую строку которых нужно проверять на предмет нарушения целостности. Более эффективная стратегия предусматривает отключение ограничения, загрузку данных и последующую проверку корректности загруженных данных. После завершения загрузки ограничения вновь вводятся в действие посредством включения. Когда ограничение отключается,как описано здесь, база данных уничтожает индекс. Подобным образом реализуется лучшая стратегия — предварительное создание неуникальных индексов для ограничений, которые база данных не должна уничтожать, поскольку они обрабатывают дублированные записи.

На заметку! Состояние enabled (включено) — это состояние ограничений Oracle по умолчанию

Отключаются ограничения двумя способами: указание в качестве состояния ограничения либо disable validate (отключить с проверкой), либо disable no validate (отключить без проверки) с использованием конструкции DISABLE VALIDATE и DISABLE NO VALIDATE, соответственно. Аналогично, для включения ограничения применяются конструкции ENABLE VALIDATE и ENABLE NO VALIDATE. Ниже кратко обсуждаются различные способы включения и отключения ограничений.

Состояние Disable Validate

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

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

Обратите внимание, что поскольку было решено сохранить ограничение в отключенном состоянии, никаких DML-действий в отношении таблицы выполнять нельзя. Эта опция идеально подходит для крупных таблиц хранилищ данных, которые обычно используются только для извлечения данных по запросам.

Состояние Disable No Validate

При отключении ограничения без проверки ограничение отключается, и нет никаких гарантий соответствия данных требованиям ограничения, поскольку Oracle не предпринимает проверку его действительности. Это по существу то же самое, что команда DISABLE CONSTRAINT.

Состояние Enable Validate

Это состояние ограничения включает ограничение, гарантируя проверку всех данных на соответствие условию ограничения. Это состояние в точности то же, что обычное состояние “включено”. Следующий пример демонстрирует использование этого состояния:

Состояние Enable No Validate

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

Более подходящая стратегия заключалась бы в использовании состояния DISABLE NOVALIDATE при загрузке данных, с переходом к состоянию ENABLE NOVALIDATE и затем к ENABLE VALIDATE в конце цикла извлечения, трансформации и загрузки (extraction,transformation, loading — ETL).

Ограничения Rely

Обычно перед загрузкой в таблицы хранилища данных предполагается выполнение шагов ETL. Если есть уверенность в качестве данных, можно сэкономить время на загрузку, отключив проверку ограничений. Для этого применяется команда ALTER TABLE с конструкцией RELY DISABLE NOVALIDATE, как показано в следующем примере:

Отложенные и немедленные ограничения

В дополнение к указанию типа проверки достоверности ограничения можно специфицировать, когда именно должно проверяться ограничение в процессе загрузки данных.

Если хотите, чтобы ограничение проверялось немедленно после модификации данных, выберите опцию not defferable (не откладывая), которая, фактически задает поведение по умолчанию в базах данных Oracle. Если необходимо выполнить едино-временную проверку ограничения после фиксации всей транзакции, выберите опцию deferrable. Все ограничения и внешние ключи могут быть объявлены как deferrable или not deferrable.

В случае выбора опции deferrable появляются еще две дополнительных опции.Ограничение deferrable можно специфицировать либо как initially deferred, либо как initially immediate (изначально отложенное или изначально немедленное). В первом случае база данных откладывает проверки ограничения до окончания транзакции. Если же была выбрана опция initially immediate, то база данных проверит ограничения перед изменением любых данных. Обратите внимание, что если предварительно создается индекс, то он должен быть не уникальным, чтобы обработать дублированные значения.

Следующий пример показывает, как специфицировать такого рода ограничения в таблице employee:

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

Представления, относящиеся к ограничениям и индексам

DBA_CONSTRAINTS

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

Следующий запрос позволит узнать, какие ограничения установлены для таблицы TESTD. Ответ на запрос показывает, что таблица имеет единственное ограничение CHECK. Префикс SYS в столбце NAME отражает тот факт, что CONSTRAINT_NAME — имя по умолчанию, а не явно указанное владельцем таблицы.

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

DBA_CONS_COLUMNS

Представление DBA_CONS_COLUMNS показывает имя столбца и позицию в таблице,где определено ограничение:

Все величины, заносимые в таблицу, обязаны входить в множество допускаемых типов соответствующего столбца. Ограничения целостности данных позволяют добавить для них требования, дополнительные к соблюдению типа. Заявляемые (схемные, формальные, "декларативные") ограничения целостности записываются ("провозглашаются") в виде условий, которые должны соблюдаться явно как таковые, на уровне схемы данных, и этим отличаются от правил целостности, сформулированных в виде запрограммированных проверок (см. ниже). Поэтому иначе такие ограничения можно называть "явными". Оригинальный термин имеет полное название " integrity data constraints" — "ограничения на значения данных, налагаемые для более точного учета обстоятельств предметной области ", но часто сокращается до " integrity constraints " или даже просто "constraints". Слово " integrity " вряд ли хорошо понятно массам разработчиков.

Само понятие заявляемых ограничений целостности в SQL было унаследовано от реляционной модели и усложнялось вместе с развитием стандарта. В Oracle номенклатура ограничений целостности в целом соответствует SQL -92 (при том, что объем реализации не выдержан), но не доведена до уровня SQL :1999. Так, Oracle не позволяет завести ограничение целостности на уровне БД (с помощью служебного слова ASSERTION ) и сильно ограничен в формулировании условия проверки значений конструкцией CHECK тем, что не допускает обращения к данным базы.

Слово ASSERTION из стандарта SQL подсказывает еще один перевод (и понимание) integrity constraints , как "утвердительные ограничения целостности".

Заявляемые ограничения целостности в Oracle можно задавать на уровнях:

отдельного поля строки в таблице;
отдельной строки;
пары таблиц.

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

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

ALTER TABLE … MODIFY — добавление ограничений всех видов и снятие ограничения NOT NULL ;
ALTER TABLE … ADD/DROP — добавление и снятие ограничений всех видов, кроме NOT NULL .

Всем ограничениям целостности, сформулированными в схеме, Oracle сообщает имена. Если при создании ограничения употребить конструкцию CONSTRAINT имя , ограничение получит имя от программиста, в противном случае СУБД создаст имя по своему усмотрению. Сведения о каждом существующем ограничении можно найти в таблице словаря-справочника USER_CONSTRAINTS по его имени. Неудачное имя ограничения можно изменить; к примеру:

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

Ограничение NOT NULL

Ограничение NOT NULL обязывает столбец или группу столбцов всегда иметь значение (если группа — то хотя бы в одном поле). Требование непустоты столбца крайне желательно, так как избавляет программиста от многочисленных забот, связанных с особенностями обработки NULL . К сожалению, требования предметной области и некоторые действия в SQL (например, GROUP BY ROLLUP … ) не позволяют совсем отказаться от столбцов со свойством NULL .

Это единственное из ограничений целостности, информация о котором хранится не только в таблице USER_CONSTRAINTS , но и в таблице USER_TAB_COLUMNS в качестве свойства столбца. (Когда-то признак NULL/NOT NULL формально считался свойством столбца, а не ограничением целостности). По этой причине добавление и упразднение этого ограничения оформляется по правилам изменения свойства столбца, только через ключевое слово MODIFY :

В современных версиях Oracle самостоятельное ограничение NOT NULL будет оформлено технически как ограничение вида CHECK с условием для проверки: budget IS NOT NULL и одновременно будет зафиксировано в USER_CONSTRAINTS значением NULLABLE = 'Y' . Свойство NOT NULL , вытекающее из правила первичного ключа, будет отражено только в USER_CONSTRAINTS .

Первичные ключи

От столбцов, назначенных первичным ключом, требуется, чтобы значения в их полях всех строк были уникальными и имелись всегда (для ключа из нескольких столбцов значение должно быть хотя бы в одном поле). Примеры создания и удаления:

Значения в полях первичного ключа должны существовать всегда.

Некоторые типы столбцов не допускаются до формирования первичного ключа (например, LOB или TIMESTAMP WITH TIME ZONE ).

Уникальность значений в столбцах

От столбцов, назначенных уникальными, требуется, чтобы значения в их полях всех строк были уникальными. Уникальность в SQL наиболее близка к понятию "альтернативного", "возможного" (candidate) или же просто "ключа" в реляционной модели.

Обратите внимание, что в столбце PNAME не запрещаются пропуски значений. По стандарту SQL уникальность отслеживается для имеющихся значений столбца. Если на такой столбец дополнительно наложить ограничение

он сможет играть роль ключа в реляционной модели и быть объявлен первичным (путем замены двух ограничений: UNIQUE и NOT NULL на одно PRIMARY KEY ). Если же уникальной объявляется группа столбцов, сообщить ей свойства ключа средствами SQL сложнее (обязательность хотя бы одного значения в уникальной группе можно потребовать ограничением вида CHECK ).

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

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

Более того, Oracle не запретит включить в состав уникальной группы столбцы первичного ключа, в том числе все из них. Последнее в реляционной теории соответствует понятию "суперключа" и невозможно для ключа.

Однако точное повторение списка имен столбцов в новом определении приведет к ошибке (что довольно необычно логически и вызвано техническими причинами реализации):

Внешние ключи

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

По правилам внешнего ключа в столбце LDEPT не запрещаются пропуски значений. Стандарт SQL требует от СУБД проверки соответствия значениям в столбцах-адресатах таблицы только имеющихся значений внешнего ключа; иными словами, значения в полях внешнего ключа могут отсутствовать.

Внешних ключей в таблице может быть определено несколько. Например, при более тщательном моделировании примера "сотрудники — отделы" в дополнение к имеющемуся внешнему ключу DEPTNO таблицы EMP можно было бы объявить внешним ключом столбец JOB , заставив его ссылаться на отдельную таблицу с описаниями штатных должностей.

Утверждение, представленное в качестве эпиграфа, взято из документации Oracle, но вся практика до прочтения документации указывала на противоположное. Проверка путём создания пары Unique Constraint -ов подтвердила это. Налицо ошибка в документации.

А что ещё (с надеждой на безошибочность описания) можно почерпнуть из документации об Ограничениях целостности в Oracle? Я постарался выписать различные терминологические и функциональные особенности Ограничений целостности как отдельных типов объектов БД Oracle без углубления в синтаксис и подробности их использования. Многое для меня оказалось новым, не буду скрывать.

Начнём с самого начала – Oracle9i Database Concepts Release 2 (9.2). В документации выделяется понятие «Целостность данных» ( Data Integrity ), которое связывается с выполнением бизнес-правил, сопряжённых с БД. Data Integrity делится на пять типов правил, часть из которых обеспечивается «Ограничениями целостности» ( Integrity Constraints ) СУБД Oracle :

1. NULL -правило – NOT NULL ограничение;

2. уникальные значения – ограничения уникального ключа;

3. значения первичного ключа – ограничения первичного ключа;

4. правила ссылочной целостности – ограничения внешнего ключа (или «ограничения ссылочной целостности» – в документации Oracle встречаются оба названия);

5. проверка комплексного ограничения – Check -ограничения.

(Здесь слева от тире представлено правило «Целостности данных», а справа – тип «Ограничений целостности», реализующий это правило)

Четвёртый тип правил «Целостности данных» является составным, и обеспечивается «Ограничениями целостности» лишь частично:

1. выставление в NULL зависимых данных при удалении справочных данных;

2. каскадное удаление зависимых данных при удалении справочных данных;

3. а также отсутствие какого либо действия над зависимыми данными при изменении или удалении справочных данных. (Здесь для меня осталась неясность в плане отличия Restrict от No Action . Может, кто из читателей поможет обнаружить различие…)

Оставшиеся существующие подтипы четвёртого пункта «Целостности данных»:

o выставление в NULL зависимых данных при изменении справочных данных;

o каскадное изменении зависимых данных при изменении справочных данных;

o выставление в значение по умолчанию зависимых данных при изменении или удалении справочных данных;

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

Далее для краткости и в силу привычки буду использовать названия «Ограничений целостности» в английском варианте (соотнесение с вышеупомянутыми русскими названиями, на мой взгляд, очевидно), а вместо «Ограничения целостности» писать просто Ограничения.

Итак, UNIQUE Key Constraint . Это Ограничение требует, чтобы каждое значение в поле ключа было уникальным. Под понятием «значение» здесь подразумевается определённая величина, а NULL-значение под данное определение не подпадает, так что одно, два, да даже все поля в ключе UNIQUE Key Constraint могут быть равны NULL. В отличие от ключа PRIMARY Key Constraint , в котором NULL-значение не допускается вовсе.

При создании UNIQUE Key Constraints или PRIMARY Key Constraints неявно создаётся уникальный индекс по тем полям таблицы, на которые накладывается данное Ограничение. Однако, если некий (неважно – уникальный или неуникальный) индекс по полям ключа уже используется, то будет использоваться именно он вместо неявного создания нового. При удалении этих Ограничений будут удаляться и индексы. Уникальные Ограничения, созданные с атрибутом DEFERRABLE (см. ниже) всегда используют неуникальные индексы. При удалении таких Ограничений неуникальные индексы остаются.

Referential Integrity Constraint требует существования в родительской ( справочной ) таблице UNIQUE Key Constraint или PRIMARY Key Constraint. При отсутствии Ограничения NOT NULL на каком либо поле, входящем в Referential Integrity Constraint , в этом поле

допускается NULL -значение, и такой Referential Integrity Constraint будет считаться правильным.

Если на внешнем ключе отсутствует индекс. Тогда при удалении или изменении первичного ключа родительской таблицы, Oracle будет выставлять блокировку дочерней таблицы на уровне таблицы, освобождая эту блокировку сразу после её получения. Если внешний ключ определён как ON DELETE CASCADE , то удаление записей из родительской таблицы будет приводить к share-subexclusive блокировкам на дочерней таблице. Разделяемая блокировка всей дочерней таблицы также потребуется при изменении тех полей в родительской таблице, на которые ссылаются поля дочерней таблицы. Разделяемая блокировка позволяет только чтение данных, так что ни вставка, ни удаление, ни изменение данных в дочерней таблице не будут доступны до тех пор, пока не завершится транзакция на родительской таблице.
Если на внешнем ключе присутствует индекс, то никаких блокировок на уровне таблицы уже не будет, и при любом удалении или изменении данных в родительской таблице, в дочерней таблице будут блокированы до завершения транзакции только отдельные соответствующие записи (эксклюзивная блокировка на уровне строк).

CHECK Integrity Constraints . Допускаются на одном или нескольких полях таблицы и требует в качестве результата выполнения определённого условия TRUE или UNKNOWN для каждой строки таблицы. Примечательно, что под UNKNOWN подразумевается… NULL! Иными словами, если везде (во всяком случае, следуя той же документации Oracle) NULL -значение не равно ничему, в том числе и самому себе, то здесь оно «работает» как TRUE . Забавно, не так ли?

может использоваться только Булево выражение;
нельзя использовать подзапросы, SQL-функции или последовательности (интересно, почему?);
нельзя использовать SYSDATE , UID , USE R, USERENV , LEVEL , ROWNUM .

Количество CHECK Integrity Constraints неограниченно, но порядок их срабатывания непредсказуем. Ну, и при использовании строчных литералов или таких SQL -функций, как TO_CHAR, TO_DATE, TO_NUMBER с параметрами поддержки глобализации в качестве аргументов, Oracle использует значения этих параметров по умолчанию на уровне базы. Эти значения можно переписать в создаваемом CHECK Integrity Constraint .

Все перечисленные Ограничения, реализованные в Ora c le, допускают их нарушение на уровне оператора, то есть сначала оператор будет полностью выполнен (пускай он коснётся хоть миллиона строк), а потом начнётся проверка Ограничений. Хотя, возможна отложенная проверка Ограничений– до завершения транзакции (о чём речь далее).

Режим SET CONSTRAINTS.

Оператор SET CONSTRAINTS делает Ограничения или DEFERRED , или IMMEDIATE ( DEFERRED и IMMEDIATE относятся к атрибутам Ограничений, о чём речь далее) для части транзакции. Данный оператор можно использовать для установки режима либо для списка Ограничений, либо для всех ( ALL ) Ограничений. Действие данного оператора заканчивается вместе с завершением текущей транзакции, либо с началом действия ещё одного такого же оператора. Данный оператор недоступен в триггерах.

SET CONSTRAINTS … IMMEDIATE вначале вызывает проверку наличия отложенных ранее срабатываний Ограничений, а потом уже срабатывают Ограничения, вызванные выполняющимися операторами в текущей транзакции. Любое нарушение Ограничения при таком процессе будет просигнализировано ошибкой, а при достижении COMMIT’а будет вызван полный откат текущей транзакции. Оператор ALTER SESSION также может иметь выражение SET CONSTRAINTS , но только для всех Ограничений (нельзя их перечислить списком). Это эквивалентно выполнению оператора SET CONSTRAINTS в самом начале каждой транзакции.

Выполнение оператора SET CONSTRAINTS … IMMEDIATE перед самым завершением транзакции позволяет определить успешность предстоящего COMMIT’а и избежать лишних откатов.

Состояния Ограничений.

С помощью операторов CREATE TABL E или ALTER TABLE можно задавать состояние каждого Ограничения на уровне таблицы, используя следующие выражения:

ENABLE гарантирует удовлетворение всех входных данных Ограничению;
DISABLE позволяет входным данным не соответствовать Ограничению;
VALIDATE гарантирует, что все уже имеющиеся в таблице данные соответствуют Ограничению;
NOVALIDATE позволяет уже имеющимся в таблице данным не соответствовать Ограничению;

…и их комбинации:

ENABLE VALIDATE аналогично ENABLE и гарантирует, что абсолютно все (и уже вставленные, и вставляемые) записи удовлетворяют Ограничению;
ENABLE NO VALIDATE гарантирует удовлетворение Ограничению всех входных данных, однако уже имеющиеся в таблице данные могут не соответствовать Ограничению;
DISABLE NOVALIDATE аналогично DISABLE . Не гарантируется удовлетворение Ограничению как входных данных, так и уже имеющихся в таблице;
DISABLE VALIDATE отключает Ограничение, удаляет индекс, на котором оно строилось, и запрещает любые изменения на полях, входящих в Ограничение.

… и немного об особенностях применения:

· выражение ENABLE подразумевает ENABLE VALIDATE ;

· выражение DISABLE подразумевает DISABLE NOVALIDATE ;

· VALIDATE и NOVALIDATE ничего не подразумевают в отношении ENABLE и DISABLE (скажем так, они являются зависимой частью выражения при ENABLE и DISABLE );

· про создание и удаление индексов уже упоминалось;

· при изменении состояния из NOVALIDATE в VALIDATE выполняется проверка всех имеющихся в таблице данных, что может занять очень много времени. Наоборот, при приведении состояния Ограничения из VALIDATE в NOVALIDATE просто «забывается», что имеющиеся данные когда-то соответствовали Ограничению;

· перевод одиночного ограничения из состояния ENABLE NO VALIDATE в состояние ENABLE VALIDATE не блокирует чтения, записи или другие DDL операции, они могут быть выполнены параллельно.

Читайте также: