«A table or view can have only one unique key .»
Oracle9i SQL Reference об ограничении использования Ограничений уникальности

Утверждение, представленное в качестве эпиграфа, взято из документации 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 .

Все перечисленные Ограничения, реализованные в Orac 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 TABLE или ALTER TABLE можно задавать состояние каждого Ограничения на уровне таблицы, используя следующие выражения:

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

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

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

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

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

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

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

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

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

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

И последние важные замечания.

• При создании Ограничения можно указать в качестве атрибута, возможно ли в дальнейшем в ходе транзакции установить оператором SET CONSTRAINTS (см. выше) отложенную (DEFERRED ) проверку данного Ограничения. По умолчанию выставляется NOT DEFERRABLE (что оно означает, думаю, понятно). После создания Ограничения изменить значение выставленного атрибута нельзя, кроме как пересоздав Ограничение, так что «семь раз подумай»!
• INIT IALLY является дополнительным атрибутом к DEFERRABLE, который может быть переписан оператором SET CONSTRAINT (опять см. выше), и который определяет поведение по умолчанию при срабатывании Ограничения с установленным атрибутом DEFERRABLE. При создании Ограничения по умолчанию выставляется INIT IALLY IMMEDIATE , тогда срабатывание Ограничения будет происходить каждый раз при выполнении отдельного оператора, при выставлении INIT IALLY DEFERRED срабатывание Ограничения будет отложено до окончания каждой транзакции. Для NOT DEFERRABLE такого дополнительного атрибута не требуется, так как он является INIT IALLY IMMEDIATE по определению.
• Выражение RELY. По умолчанию при изменении Ограничения (с помощью ALTER TABLE или MODIFY constraint ) выставляется NORELY . А означает оно следующее: стоит ли Oracle"у принимать в расчёт Ограничение, находящеесяв состоянии NOVALIDATE, для перезаписи запроса? RELY активирует существующее Ограничение в режиме (здесь в документации SQL Reference используется слово «mode », хотя я уже привык к слову «state» из Concepts , - по-видимому, писали разные люди, позабыв договориться о терминах) NOVALIDATE, что позволит переписать запрос, который иначе мог быть переписанным только с Ограничением в режиме VALIDATE. Примерно так. Подробнее - в следующий раз.
• EXCEPTIONS INTO определяет схему и таблицу, в которую будут внесены ROWID, нарушающие Ограничение при изменении его (Ограничения) состояния. Если имя схемы и таблицы не указать, то будет предполагаться, что нужно использовать таблицу с именем EXCEPTIONS в текущей схеме.

Вот и всё. На сегодня.

Целостность данных - это механизм поддержания соответствия базы данных предметной области. В реляционной модели данных определены два базовых требования обеспечения целостности:

целостность ссылок

целостность отношений.

Целостность отношений.

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

каждый кортеж любого отношения должен отличатся от любого другого кортежа этого отношения (т.е. любое отношение должно обладать первичным ключом).

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

при добавлении записей в таблицу проверяется уникальность их первичных ключей

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

Целостность ссылок

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

Связи между данными отношениями описываются в терминах функциональных зависимостей.

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

для каждого значения внешнего ключа, появляющегося в дочернем отношении, в родительском отношении должен найтись кортеж с таким же значением первичного ключа.

Пусть, например, даны отношения ОТДЕЛ (N_ОТДЕЛА , ИМЯ_ОТДЕЛА) и СОТРУДНИК (N_СОТРУДНИКА , N_ОТДЕЛА, ИМЯ_СОТРУДНИКА), в которых хранятся сведения о работниках предприятия и подразделениях, где они работают. Отношение ОТДЕЛ в данной паре является родительским, поэтому его первичный ключ "N_отдела" присутствует в дочернем отношении СОТРУДНИК. Требование целостности по ссылкам означает здесь, что в таблице СОТРУДНИК не может присутствовать кортеж со значением атрибута "N_отдела" , которое не встречается в таблице ОТДЕЛ. Если такое значение в отношении ОТДЕЛ отсутствует, значение внешнего ключа в отношении СОТРУДНИК считается неопределенным.

Как правило, поддержание целостности ссылок также возлагается на систему управления базой данных.

Существуют две основные стратегии поддержания ссылочной целостности :

RESTRICT (ОГРАНИЧИТЬ) - не разрешать выполнение операции, приводящей к нарушению ссылочной целостности. Это самая простая стратегия, требующая только проверки, имеются ли кортежи в дочернем отношении, связанные с некоторым кортежем в родительском отношении.

CASCADE (КАСКАДИРОВАТЬ) - разрешить выполнение требуемой операции, но внести при этом необходимые поправки в других отношениях так, чтобы не допустить нарушения ссылочной целостности и сохранить все имеющиеся связи. Изменение начинается в родительском отношении и каскадно выполняется в дочернем отношении. В реализации этой стратегии имеется одна тонкость, заключающаяся в том, что дочернее отношение само может быть родительским для некоторого третьего отношения. При этом может дополнительно потребоваться выполнение какой-либо стратегии и для этой связи и т.д. Если при этом какая-либо из каскадных операций (любого уровня) не может быть выполнена, то необходимо отказаться от первоначальной операции и вернуть базу данных в исходное состояние. Это самая сложная стратегия, но она хороша тем, что при этом не нарушается связь между кортежами родительского и дочернего отношений.

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

Поскольку отношение – это множество, а множества по определению не содержат совпадающих элементов, то никакие два кортежа отношения не могут быть дубликатами друг друга в любой произвольно-заданный момент времени. Пусть R – отношение с атрибутами A1, A2, ..., An. Говорят, что множество атрибутов K=(Ai, Aj, ..., Ak) отношения R является возможным ключом R тогда и только тогда, когда удовлетворяются два независимых от времени условия:

Уникальность: в произвольный заданный момент времени никакие два различных кортежа R не имеют одного и того же значения для Ai, Aj, ..., Ak.

Минимальность: ни один из атрибутов Ai, Aj, ..., Ak не может быть исключен из K без нарушения уникальности.

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

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

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

Формальное определение.

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

Замечание . Внешний ключ, также как и возможный, может быть простым и составным.

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

Замечание . Внешний ключ, как правило, не обладает свойством уникальности . Так и должно быть, т.к. в дочернем отношении может быть несколько кортежей, ссылающихся на один и тот же кортеж родительского отношения. Это, собственно, и дает тип отношения "один-ко-многим".

Замечание . Для внешнего ключа не требуется, чтобы он был компонентом некоторого возможного ключа.

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

Отношение – Таблица (иногда Файл), Кортеж – Строка (иногда Запись), Атрибут – Столбец, Поле.

7. Ограничения целостности

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

7.1 Что такое ограничения целостности

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

Вначале - немного теории.

Все ограничения целостности можно разделить на три большие категории:

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

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

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

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

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

Свойство (С) - согласованность транзакций определяется наличием понятия согласованности базы данных.

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

Определение 3 . База данных находится в согласованном (целостном) состоянии , если выполнены (удовлетворены) все ограничения целостности, определённые для базы данных.

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

- Отказ выполнить "незаконную" операцию.

Выполнение компенсирующих действий.

17.3. Классификация ограничений целостности.

Ограничения целостности можно классифицировать несколькими способами:

• По способам реализации.
• По времени проверки.
• По области действия.

17.3.1. Классификация ограничений целостности по способам реализации

Каждая система обладает своими средствами поддержки ограничений целостности. Различают два способа реализации:

• Декларативная поддержка ограничений целостности.
• Процедурная поддержка ограничений целостности.

Определение 4 . Декларативная поддержка ограничений целостности заключается в определении ограничений средствами языка определения данных (DDL - Data Definition Language). Обычно средства декларативной поддержки целостности (если они имеются в СУБД) определяют ограничения на значения доменов и атрибутов, целостность сущностей (потенциальные ключи отношений) и ссылочную целостность (целостность внешних ключей). Декларативные ограничения целостности можно использовать при создании и модификации таблиц средствами языка DDL или в виде отдельных утверждений (ASSERTION).

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

Не все ограничения целостности можно реализовать декларативно. По сути, наличие ограничения целостности (как декларативного, так и процедурного характера) всегда приводит к созданию или использованию некоторого программного кода, реализующего это ограничение. Разница заключается в том, где такой код хранится и как он создаётся.

17.3.2. Классификация ограничений целостности по времени проверки.

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

• Немедленно проверяемые ограничения.
• Ограничения с отложенной проверкой.

Определение 6 . Немедленно проверяемые ограничения проверяются непосредственно в момент выполнения операции, могущей нарушить ограничение. Например, проверка уникальности потенциального ключа проверяется в момент вставки записи в таблицу. Если ограничение нарушается, то такая операция отвергается. Транзакция, внутри которой произошло нарушение немедленно проверяемого утверждения целостности, обычно откатывается.

Определение 7 . Ограничения с отложенной проверкой проверяется в момент фиксации транзакции оператором COMMIT WORK. Внутри транзакции ограничение может не выполняться. Если в момент фиксации транзакции обнаруживается нарушение ограничения с отложенной проверкой, то транзакция откатывается.

17.3.3. Классификация ограничений целостности по области действия.

По области действия ограничения делятся на:

• Ограничения домена
• Ограничения атрибута
• Ограничения кортежа
• Ограничения отношения
• Ограничения базы данных

17.3.3.1. Ограничения домена.

Определение 8 . Ограничения целостности домена представляют собой ограничения, накладываемые только на допустимые значения домена. Фактически, ограничения домена обязаны являться частью определения домена.

Проверка ограничения . Ограничения домена сами по себе не проверяются. Если на каком-либо домене основан атрибут, то ограничение соответствующего домена становится ограничением этого атрибута.

17.3.3.2. Ограничения атрибута.

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

Проверка ограничения . Ограничение атрибута является немедленно проверяемым ограничением. Действительно, ограничение атрибута не зависит ни от каких других объектов базы данных, кроме домена, на котором основан атрибут. Поэтому никакие изменения в других объектах не могут повлиять на истинность ограничения.

17.3.3.3. Ограничения кортежа.

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

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

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

17.3.3.4. Ограничения отношения.

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

Проверка ограничения . К моменту проверки ограничения отношения должны быть проверены ограничения целостности кортежей этого отношения.

Ограничение отношения может быть как немедленно проверяемым с отложенной проверкой .

Ограничение отношения, являющееся ограничением потенциального ключа является немедленно проверяемым ограничением.

Ограничение, определённое наличием функциональной зависимости атрибутов также является немедленно проверяемым ограничением.

Ограничения же, определённые многозначной зависимостью или зависимостью соединения являются ограничениями с отложенной проверкой. Действительно, эти ограничения требуют, чтобы кортежи вставлялись и удалялись целыми группами . Это невозможно сделать, если выполнять проверку после каждой одиночной вставки или удаления кортежа.

17.3.3.5. Ограничения базы данных.

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

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

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

Ограничение отношения, являющееся ограничением внешнего ключа может быть как немедленно проверяемым ограничением, так и отложенным ограничением.

Ограничения целостности данных позволяют добавить для них требования, дополнительные к соблюдению типа. Заявляемые (схемные, формальные, "декларативные") ограничения целостности записываются ("провозглашаются") в виде условий, которые должны соблюдаться явно как таковые , на уровне схемы данных, и этим отличаются от правил целостности, сформулированных в виде запрограммированных проверок (см. ниже). Поэтому иначе такие ограничения можно называть "явными". Оригинальный термин имеет полное название " 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 по его имени. Неудачное имя ограничения можно изменить; к примеру:

ALTER TABLE projx RENAME CONSTRAINT sys_c0011509 TO name_is_needed;

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

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

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

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

ALTER TABLE proj MODIFY (budget NOT NULL); -- создание ограничения с системным именем; скобки необязательны ALTER TABLE proj MODIFY (budget NULL); -- упразднение ограничения; скобки необязательны ALTER TABLE proj MODIFY (budget CONSTRAINT is_mandatory NOT NULL); -- создание ограничения с именем, заданным программистом

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

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

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

ALTER TABLE proj ADD PRIMARY KEY (projno, pname); -- создание ограничения (первичный ключ на основе двух столбцов) с системным именем ALTER TABLE proj DROP PRIMARY KEY; -- упразднение ограничения ALTER TABLE proj ADD CONSTRAINT pk_proj PRIMARY KEY (projno); -- создание ограничения с именем, заданным программистом

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

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

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

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

Пример создания:

ALTER TABLE proj ADD UNIQUE (pname);

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

ALTER TABLE proj MODIFY (pname NOT NULL);

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

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

ALTER TABLE t ADD CONSTRAINT xx UNIQUE (a, b); -- Ошибка!

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

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

ALTER TABLE proj ADD (ldept NUMBER (2)) ; ALTER TABLE proj ADD FOREIGN KEY (ldept) REFERENCES dept (deptno) ;

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

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