Определение ограничений целостности. Целостность данных. Ограничения целостности

«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 в текущей схеме.

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

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

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

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

1. Тип и формат поля.

2. Задание диапазона значений. Значения диапазона и его тип зависят от особенностей ПО.

3. Признак непустого поля. Характеризует недопустимость пустого значения поля в БД. Например, в отношении, содержащем сведения о сотрудниках, поля “фамилия”, ”имя”, ”отчество”, ”оклад” должны обязательно иметь какое-то значение, а у поля “ученая степень” значение может отсутствовать.

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

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

Специфическим ограничением на значение поля является признак его уникальности. Это ограничение проверяет допустимость значения данного поля, но при этом просматривается вся таблица (файл).

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

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

Рассмотренные выше ограничения определяли проверку значения поля вне зависимости от того, вводится ли это значение впервые или корректируются имеющиеся в БД значения. Ограничения, которые используются только при проверке допустимости корректировки, называются ограничениями перехода. Например, если в БД имеется поле “возраст сотрудника”, то при корректировке значение этого поля может только увеличиваться. Если в БД хранится поле “год рождения”, то при корректировать это поле следует запретить.

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

В качестве примера ограничений, относящихся ко всей таблице, можно привести следующий. Предположим, что фонд заработной платы формируется исходя из величины средней зарплаты одного сотрудника и эта величина равна N руб. Тогда в качестве ограничения целостности таблицы может быть задано условие, указывающее, что среднее значение поля “оклад” должно быть не больше N. Примерами ограничения целостности, которыми проверяют соотношения между строками одной таблицы, являются следующие: 1) нельзя быть родителем и ребенком одного и того же человека; 2) год рождения родителя должен быть меньше, чем год рождения ребенка. Первый из приведенных примеров является частным случаем более общего ограничения на отсутствие циклов.

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

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

Разновидностью ограничения целостности связи является ограничение связи по существованию, заключающееся в том, что для существования объекта в отношении R1 необходимо, чтобы он был связан с объектом в отношении R2. Например, при приеме на работу каждый из работающих должен быть зачислен в какой-то отдел, и соответствующая запись в таблице “Кадры” в поле “отдел” должна иметь значение, совпадающее с одним из значений соответствующего поля в таблице “Отделы”.

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

Своеобразным видом ограничений целостности является запрет на обновление. Он может быть обусловлен технологией обработки данных или спецификой ПО. Так, если описывается объект “Личность”, то такие атрибуты, как дата рождения и место рождения, являются постоянными (статическими) и меняться не могут.

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

Очень важным видом ограничений целостности являются функциональные зависимости. Информация об имеющихся в данной ПО функциональных зависимостях фиксируется в ИЛМ и используется при проектировании БД и для контроля целостности при функционировании БД. Для соответствующих полей в БД желательно задать запрет на обновление.

Запрет на обновление может относиться не только к отдельному полю, но и ко всей строке (записи) и к таблице.

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

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

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

СУБД FoxPro обеспечивает целостность при корректировке, если предусмотрена соответствующая связь таблиц в БД с помощью SET Relation и SET SKIP.

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

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

Рассмотренные выше примеры ограничений целостности относились к данным пользователя. Понятие целостности может относиться и к служебной информации. Это прежде всего относится к поддержанию соответствия между индексными файлами и соответствующими им индексируемыми файлами БД.

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

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

Задание ограничений целостности и их проверка являются важной частью проектирования и функционирования БнД.

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

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

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

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

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

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

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

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

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

Операции над данными

Модель данных определяет множество действий, которые допустимо производить над некоторой реализацией БД для её перевода из одного состояния в другое. Это множество соотносят с языком манипулирования данными (Data Manipulation Language, DML).

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

По типу производимых действий различают следующие операции:

идентификация данных и нахождение их позиции в БД;
выборка (чтение) данных из БД;
включение (запись) данных в БД;
удаление данных из БД;
модификация (изменение) данных БД.

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

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

Неявные ограничения определяются самой структурой данных. Например, тот факт, что запись типа СОТРУДНИК имеет поле Дата рождения , служит, по существу, ограничением целостности, означающим, что каждый сотрудник организации имеет дату рождения, причём только одну.

Явные ограничения включаются в структуру базы данных с помощью средств языка контроля данных (DCL, Data Control Language). В качестве явных ограничений чаще всего выступают условия, накладываемые на значения данных. Например, номер паспорта является уникальным, заработная плата не может быть отрицательной, а дата приёма сотрудника на работу обязательно будет меньше, чем дата его перевода на другую работу.

Также различают статические и динамические ограничения целостно-сти. Статические ограничения присущи всем состояниям ПО, а динамические определяют возможность перехода ПО из одного состояния в другое. Примерами статических ограничений целостности могут служить требование уникальности индивидуального номера налогоплательщика (ИНН) или задание ограниченного множества значений атрибута "Пол" ("м" и "ж"). В качестве примера динамического ограничения целостности можно привести правило, которое распространяется на поля-счётчики: значение счётчика не может уменьшаться.

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

В настоящее время разработано много различных моделей данных. Основные – это сетевая, иерархическая и реляционная модели.

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

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

Для создания таблиц в среде MS SQL Server используется команда:

<определение_таблицы> ::= CREATE TABLE [ имя_базы_данных.[владелец]. | владелец. ]имя_таблицы (<элемент_таблицы>[,...n])

<элемент_таблицы> ::= {<определение_столбца>} | <имя_столбца> AS <выражение> | <ограничение_таблицы>

Обычно владельцем таблицы (dbo) является тот, кто ее создал.

<Выражение> задает значение для вычисляемого столбца . Вычисляемые столбцы - это виртуальные столбцы, т. е. физически в таблице они не хранятся и вычисляются с использованием значений столбцов той же таблицы. В выражении для вычисляемого столбца могут присутствовать имена обычных столбцов, константы и функции, связанные одним или несколькими операторами. Подзапросы в таком выражении участвовать не могут. Вычисляемые столбцы могут быть включены в раздел SELECT при указании списка столбцов, которые должны быть возвращены в результате выполнения запроса. Вычисляемые столбцы не могут входить во внешний ключ , для них не используются значения по умолчанию. Кроме того, вычисляемые столбцы не могут участвовать в операциях INSERT и DELETE .

<определение_столбца> ::= { имя_столбца <тип_данных>} [ [ DEFAULT <выражение> ] | [ IDENTITY (начало, шаг) ]]] [<ограничение_столбца>][...n]]

В определении столбца обратим внимание на параметр IDENTITY , который указывает, что соответствующий столбец будет столбцом-счетчиком . Для таблицы может быть определен только один столбец с таким свойством. Можно дополнительно указать начальное значение и шаг приращения. Если эти значения не указываются, то по умолчанию они оба равны 1. Если с ключевым словом IDENTITY указано NOT FOR REPLICATION , то сервер не будет выполнять автоматического генерирования значений для этого столбца, а разрешит вставку в столбец произвольных значений.

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

<ограничение_столбца>::= [ CONSTRAINT имя_ограничения ] { [ NULL | NOT NULL ] | [ {PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR=фактор_заполнения ] [ ON {имя_группы_файлов | DEFAULT } ] ] ] | [ [ FOREIGN KEY ] REFERENCES имя_род_таблицы [(имя_столбца_род_таблицы) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ NOT FOR REPLICATION ]] | CHECK [ NOT FOR REPLICATION](<лог_выражение>) } <ограничение_таблицы>::= { [ {PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] {(имя_столбца [,...n])} ] |FOREIGN KEY[(имя_столбца [,...n])] REFERENCES имя_род_таблицы [(имя_столбца_род_таблицы [,...n])] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] | NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] (лог_выражение) }

Рассмотрим отдельные параметры представленных конструкций, связанные с ограничениями целостности данных . Ограничения целостности имеют приоритет над триггерами, правилами и значениями по умолчанию. К ограничениям целостности относятся ограничение первичного ключа PRIMARY KEY , ограничение внешнего ключа FOREIGN KEY , ограничение уникальности UNIQUE , ограничение значения NULL , ограничение на проверку CHECK .

Ограничение первичного ключа (PRIMARY KEY)

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

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

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

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

Ограничение внешнего ключа (FOREIGN KEY)

Ограничение внешнего ключа - это основной механизм для поддержания ссылочной целостности между таблицами реляционной базы данных. Столбец дочерней таблицы, определенный в качестве внешнего ключа в параметре FOREIGN KEY , применяется для ссылки на столбец родительской таблицы, являющийся в ней первичным ключом . Имя родительской таблицы и столбцы ее первичного ключа указываются в предложении REFERENCES . Данные в столбцах, определенных в качестве внешнего ключа , могут принимать только такие же значения, какие находятся в связанных с ним столбцах первичного ключа родительской таблицы. Совпадение имен столбцов для связи дочерней и родительской таблиц необязательно. Первичный ключ может быть определен для столбца с одним именем, в то время как столбец, на который наложено ограничение FOREIGN KEY , может иметь совершенно другое имя. Единственным требованием остается соответствие столбцов по типу и размеру данных.

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

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

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

Ограничение ссылочной целостности задает требование, согласно которому для каждой записи в дочерней таблице должна иметься запись в родительской таблице. При этом изменение значения столбца связи в записи родительской таблицы при наличии дочерней записи блокируется, равно как и удаление родительской записи (запрет каскадного изменения и удаления), что гарантируется параметрами ON DELETE NO ACTION и ON UPDATE NO ACTION , принятыми по умолчанию. Для разрешения каскадного воздействия следует использовать параметры ON DELETE CASCADE и ON UPDATE CASCADE .