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

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

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

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

Примеры правил: вес детали должен быть положительным; количество знаков в телефонном номере не должно превышать 15; возраст родителей не может быть меньше возраста их биологического ребенка и так далее.

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

Механизмы обеспечения целостности являются одной из составляющих концепции модели данных ^[

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

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

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

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

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

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

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

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

• Определение атрибута на некотором множестве означает, что все значения должны принадлежать этому множеству. Ограничения – значения данных (Зарплата – 0..100).
• Логические ограничения – свойство, которое для данного множества истинно или ложно. Зарплата Иванова больше зарплаты Петровой.
• Обобщенные ограничения – эти ограничения относятся к множеству объектов, а не к конкретной реализации. Зарплата руководителя больше зарплаты подчиненного.

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

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

«Проблема целостности состоит в обеспечении ... правильности данных в базе данных в любой момент времени» [14]. Целостность — актуальность и непротиворечивость информации, ее защищенность от разрушения и несанкционированного изменения.

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

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

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

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

Рисунок 1

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

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

В теории реляционных баз данных принято выделять четыре типа ограничений целостности ^:353:

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

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

Ограничения целостности могут классифицироваться по разным признакам (рис. 4.1).

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

Рис. 4.1. Общая схема классификации ограничений целостности

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

1.1. Тип и формат поля. Тип поля определяет допустимые для данного поля символы, а иногда и более жесткие ограничения на до­пустимые значения (как, например, для полей типа дата или логи­ческое).

1.2. Задание диапазона значений. Обычно используется для чис­ловых полей.

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

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

Двусторонний диапазон будет открытым, если допустимые зна­чения меньше «левой» границы и больше «правой» (рис. 4.2). Зада­ние двусторонних открытых диапазонов используется гораздо реже, чем закрытых. Некоторые СУБД поддерживают высокоуровневые средства задания двусторонних закрытых диапазонов и не поддержи­вают - открытых. Пример открытого диапазона: орган социального обеспечения поддерживает базу данных, содержащих записи о лю­дях моложе 16 лет или старше 60.

Рис. 4.2. Графическая иллюстрация понятий открытого (а)

и закрытого (б) двустороннего диапазона

1.3. Признак непустого поля. Характеризует недопустимость пу­стого значения поля в БД. Так, например, в таблице, содержащей све­дения о сотрудниках, поля «Фамилия», «Имя», «Отчество», «Оклад» должны обязательно иметь какое-то значение, а у поля «Ученая_степень» значение может отсутствовать.

1.4. Задание домена. Поле может принимать значение из задан­ного множества. Множество возможных значений какого-либо атри­бута называется доменом. Домен может задаваться перечислением входящих в него значений (например, значением поля «Пол» может быть только либо «мужской» либо «женский»; значением поля «Дол­жность» для профессорско-преподавательского состава может быть: «ассистент», «старший преподаватель», «доцент» и «профессор») или алгоритмом вычисления допустимых значений (как это обычно про­исходит для полей типа «Дата»). Последний из приведенных приме­ров свидетельствует не только о возможностях СУБД по поддержа­нию целостности данных, но и о важности процедуры выбора типа данных при проектировании баз данных.

Следует обратить внимание, что термин «домен» здесь использу­ется не как сугубо реляционный, а для обозначения множества воз­можных значений какого-либо атрибута безотносительно к использу­емой модели данных.

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

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

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

1.6. Очень важным видом ограничений целостности являются функциональные зависимости. Информацию об имеющих место в данной предметной области функциональных зависимостях можно извлечь из инфологической модели (см. разд. 4.2). Эта информация используется и при проектировании базы данных, и для контроля це­лостности при ее функционировании. Если БД спроектирована пра­вильно, т.е. она находится в 4-й нормальной форме, то, определяя ключи и вероятные ключи отношений, тем самым определяются и имеющиеся функциональные зависимости между атрибутами.

1.7. Рассмотренные выше ограничения определяли проверки зна­чения поля вне зависимости от того, вводится это значение впервые или корректируются имеющиеся в базе данных значения. Ограниче­ния, которые используются только при проверке допустимости кор­ректировки, называют ограничениями перехода (или динамическими ограничениями). Например, если в базе данных имеются поля «Возраст_сотрудника», «Стаж_работы» и т.п., то при корректировке зна­чения этих полей могут только увеличиваться. Об этом говорит сайт https://intellect.icu . В аспекте правильнос­ти проектирования БД приведенные выше для иллюстрации поля, особенно поле «Возраст_сотрудника», лучше вообще не хранить в базе данных, а получать расчетным путем. Это не только существенно уп­ростит ведение базы данных, но и облегчит процесс обеспечения це­лостности данных.

Другим примером ограничения перехода является корректировка поля «Семейное_положение». Так, значение «вдовец» может быть исправлено только на «женат», а «холост» не может быть исправлен на «разведен» и т.п.

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

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

2. Кортеж (запись, строка). Здесь имеются в виду ограничения на соотношения значений отдельных полей в пределах одной строки. В качестве ограничения на соотношения полей внутри одного корте­жа можно привести следующее: значение поля «Стаж» не должно пре­вышать [«Возраст» - 16] (предполагается, что трудовой стаж челове­ка начинается не ранее чем в 16 лет).

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

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

4. Совокупность взаимосвязанных таблиц. Эти ограничения отличаются от рассмотренных выше, которые затрагивали информа­ционные единицы в пределах одной таблицы.

4.1. Наиболее часто встречающееся из этих ограничений - огра­ничение целостности связи. Оно выражается в том, что значение ат­рибута, отражающего связь между объектами и являющегося внешним ключом отношения, обязательно должно совпадать с одним из значе­ний атрибута, являющегося ключом отношения, описывающего соот­ветствующий объект. Например, если в базе данных существует таб­лица, отражающая связь между преподавателями и дисциплинами, которые каждый из них может преподавать, то код преподавателя в этой таблице должен соответствовать одному из кодов в таблице «Пре­подаватели», а код дисциплины - значению соответствующего поля в таблице «Дисциплины».

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

4.2. Разновидностью ограничения целостности связи является ограничение по существованию, заключающееся в том, что для суще­ствования объекта в отношении S₁ необходимо, чтобы он был связан с объектом в отношении S₂.

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

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

• запись в основной таблице можно удалять только в том случае, если нет связанных с ней записей в подчиненной таблице;

• при удалении записи основной таблицы удаляются все связан­ные с ней записи в зависимой таблице (так называемое каскадное удаление);

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

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

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

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

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

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

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

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

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

Если объекты имеют статические свойства (на ER-диаграмме от­мечены буквой «С»), то для них можно задавать запрет на обновле­ние. Так, например, если описывается объект ЛИЧНОСТЬ, то такие атрибуты, как «Дата_рождения» и «Место_рождения» являются постоянными и меняться не могут. Задание запрета на обновление для соответствующих полей в базе данных гарантирует, что сохраненная в БД информации не будет случайно или преднамеренно искажена.

Рассмотрим следующий пример ограничения на обновление за­писи. Пусть в базе данных по кадровому составу для каждого сотруд­ника хранятся сведения об их поощрениях/наградах. Эта информа­ция хранится в таблице «Поощрения», имеющей поля: «Табельный_номер сотрудника», «Вид_поощрения», «Дата». В эту таблицу могут добавляться записи, но каждая отдельная запись изменяться не может.

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

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

7. Ограничения целостности можно не только накладывать, но и отменять. При этом между отношениями могут существовать зависи­мости, и отмена одного из них может потребовать ликвидации дру­гих (ссылочных) ограничений, зависящих от первоначального. На­пример, если объявлено, что в таблице, содержащей сведения об орга­низациях, поле «Наименование_организации» является уникальным и объявлена ссылочная целостность с таблицей «Поставка», в кото­рой также имеется это поле, а потом отменяется ограничение на уни­кальность поля «Наименование_организации» в первой таблице, то ссылочное ограничение целостности также должно быть удалено (по­скольку ссылочная целостность проверяется только в случае, если в главной таблице соответствующее поле является ключевым). Неко­торые СУБД автоматически поддерживают каскадное удаление огра­ничений целостности, когда при удалении одного из них удаляются все зависящие от него ограничения целостности.

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

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

С понятием отложенного ограничения целостности тесно связа­но понятие транзакции - законченной совокупности действий над БД, которая переводит БД из одного целостного в логическом смысле со­стояния в другое целостное состояние.

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

9. Другим признаком классификации по временному признаку является классификация по режиму проверки корректности БД. Возможны два режима проверки ограничений целостности: провер­ка в момент корректировки и проверка существующей БД. Назовем первый из них оперативным режимом, второй - аудитом БД.

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

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

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

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

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

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

13. Различают логическую и физическую целостность БД. Логи­ческая целостность - состояние БД, характеризующееся отсутстви­ем нарушений ограничений целостности, присущих логической мо­дели данных (т.е. неявных ограничений), и явных ограничений, за­данных декларативным или процедурным путем. Выше речь шла именно о логической целостности. Физическая целостность — от­сутствие нарушений спецификаций схемы хранения, а также физи­ческих разрушений данных на носителе.

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

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

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

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

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

В современных СУБД многие ограничения можно описать на ЯОД. Они хранятся в схеме данных и при работе с БД поддерживаются ав­томатически.

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

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

Целостность и истинность данных в БД

Целостность БД не гарантирует достоверности (истинности) содержащейся в ней информации, но обеспечивает по крайней мере правдоподобность этой информации, отвергая заведомо невероятные, невозможные значения. Таким образом, не следует путать целостность (непротиворечивость) БД с истинностью БД. Истинность и непротиворечивость — не одно и то же ^:351.

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

Из того, что данные являются правильными, следует, что они непротиворечивы (но не обратное), а из того, что данные противоречивы, следует, что они неправильны (но не обратное). Здесь под словом «правильные» подразумевается, что в базе данных содержатся правильные данные тогда и только тогда, когда она полностью отражает истинное состояние дел в реальном мире ^:351.

Ограничения для доменов и атрибутов

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

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

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

Ограничения на сущности и связи

В моделях данных (МД) ограничения могут быть заданы не только для атрибутов, но и для типов сущностей и типов связей.
Прежде всего, рассмотрим класс ограничений, задаваемых на отображениях между атрибутами и/или типами сущностей.
Отношение – отображение между двумя или более множествами атрибутов и оно является подмножеством их декартового произведения.
Пусть есть два множества атрибутов S1 и S2.
Эти два множества определяются как два отображения:
R: S1 -> S2 – отображение;
R1: S2 -> S1 – обратное отображение.
Множество S1 – идентификационный номер служащего, S2 – фамилия служащего.
Координальное число – число элементов множества S1 связанных с элементами множества S2.
Если в отношении имеется запись S1 (0, ?) – то это означает, что любой элемент в множестве S2 может быть связан минимум с 0 и максимум с ? элементов в S1.
Отношение S1 (0, ?), S2 (0, ?) является наиболее общим и имеет название «многие ко многим» или «M:N», то есть любой элемент множества S1 может отображаться на любое количество элементов множества S2 и наоборот. Наложив те или иные ограничения на минимальные и максимальные показатели можно получить различные типы отображений.
Например: Пусть S1 – сущность «Студент», а S2 – сущность «экзамены». Выберем отношение между этими сущностями: Сдача студентом (0, ?) экзаменов (4, 6).
Тем самым мы определили, что любой элемент множества студент должен сдать от 4 до 6 экзаменов.
Существуют различные виды ограничений на связи (отношений).
Например: R(S1(0, ?), S2(1,?)) – описывает, что каждый элемент множества S1 отображается в один или более элементов S2, но любой элемент множества S2 отображается в произвольное количество элементов множества S1.
Такое отображение называется функцией.
R(S1(0, 1), S2(0, ?)) – определяет функциональное отображение из S2 в S1, то есть любой объект в S2 отображается не более чем одним элементом из S1.
Пример: Пусть сущность «Служащий» имеет два атрибута: «номер» и «фамилия». Каждый из атрибутов определен на своем домене:
Номер -> (101, 57, 85);
Фамилия -> (Иванов, Петров, Сидоров).
Если нет ограничений, то расширением этого интенсионала, может быть следующее:

Пусть имеется ограничение: никакие служащие не имеют одинаковых номеров. Тогда расширение является недостоверным и необходимо обеспечить функциональное отображение между атрибутами «номер» и «фамилия», которое записывается в виде:
Номер => фамилия.
Это отношение называется «один к одному», то есть каждому элементу множества S1 соответствует один и только один элемент множества S2, и наоборот.
Существует еще один вид отображений «один к N» или «N к одному» – «один ко многим».
Например: служащие и отделы, в которых они работают.
Существуют полные и частичные отображения.
Частичное отображение – такое отображение, когда элемент из множества S1 отображается в 0 или более элементов множества S2, а элемент из множества S2 не более чем в один элемент множества S1, но не для всех элементов множества S2 это отображение определено.
R(S1(0, 1):S2(0, ?))
Например: каждый служащий работает не более чем в одном отделе, однако не все отделы имеют служащих.
Полное отображение – такое отображение, когда каждый элемент из множества S2 отображается точно одним элементом из множества S1, а элемент из множества S1 отображается в 0 или более элементов S2.
Например: каждый служащий должен быть закреплен за каким-либо отделом.
Существует ряд ограничений, которые нельзя выразить как ограничения на отображения.
Например: зарплата подчиненных меньше зарплаты руководителя.

Такие ограничения должны задаваться явными предикатами.

Практика

1 Средства ограничения целостности данных в SQL Server

Ограничения целостности в базах данных SQL Server, CHECK, пользовательские типы данных (user-defined types, UDF), RULE, первичный ключ (PRIMARY KEY), ограничения уникальности (UNIQUE)

Главное средство обеспечение доменной целостности в SQL Server - это ограничение CHECK. Оно может быть определено при создании таблицы или добавлено позднее при помощи команды ALTER TABLE, например:

ALTER TABLE dbo.Employees

ADD

CONSTRAINT CK_birthdate

CHECK (BirthDate > '01-01-1900' AND BirthDate < getdate())

На графическом экране ограничение можно создать (или получить информацию/изменить/удалить) на графическом экране Enterprise Manager, открыв таблицу в режиме Design Table, а затем нажав на кнопку Manage Constraints. Про ограничение CHECK необходимо сказать, что:

· можно проверять соответствие только константным значениям (диапазону значений). Использовать подзапросы в ограничении нельзя.

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

Практически полный аналог Check в SQL Server - это Rule, правило. Фактически этот тот же самый CHECK, но создаваемый как отдельный объект базы данных. В результате созданное правило мы можем привязывать ко множеству столбцов в базе:

CREATE RULE id_chk AS @id BETWEEN 0 and 10000

GO

sp_bindrule id_chk, 'cust_sample.cust_id'

GO

На графическом экране Enterprise Manager работа с правилами производится из контейнера Rules под контейнером баз данных.

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

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

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

ALTER TABLE doc_exe

ADD column_b INT IDENTITY

CONSTRAINT column_b_pk PRIMARY KEY

На графическом экране - так же открываем таблицу в режиме Design table и нажимаем на кнопку Manage Indexes/Keys. Оттуда же создаем и ограничение UNIQUE - средство обеспечения уникальности значений без первичного ключа.

Ссылочная целостность, обеспечивается, как уже говорилось, системой первичных и внешних ключей. Создание их - см. создание первичного ключа. Можно определять их как при создании таблицы, так и после, средствами TSQL (ALTER TABLE) или Enterprise Manager.

2 Средства ограничения целостности данных в Microsoft Access

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

В Microsoft Access средств реализации целостности на уровне ядра намного меньше. Практически это - только система первичных и внешних ключей плюс условия на проверку в свойствах столбцов. На практике получается так, что больших требований к ядру при работе с Access обычно предъявлять не приходится - все проверки реализуются на клиенте, например, средствами VBA.

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

Лабораторная работа - Работа с ограничениями целостности

Ограничения целостности на Access и SQL Server, преобразование ограничений целостности при переносе баз данных

Подготовка:

Создайте базу данных C:\Constraints.mdb, а в ней - единственную таблицу MeasuresDateTime с двумя столбцами:

Название столбца	Тип данных	Доп. свойства:
DateTimeID	Счетчик	Первичный ключ
DateTimeField	Дата/Время

Задание:

1) Настройте для данной таблицы ограничение: в поле DateTimeField нельзя вводить даты позднее текущего числа. Убедитесь, что данное ограничение действует.

2) Перенесите таблицу MeasuresDateTime в новую базу данных ConstraintsSQL на ваш локальный SQL Server с переносом созданного ограничения и просмотрите его определение.

Решение:

К пункту 1 - создание ограничения целостности:

1) В созданной вами базе данных C:\Constraints.mdb откройте таблицу MeasuresDateTime и выделите столбец DateTimeField.

2) Установите указатель в поле "Условие на значение" и нажмите на появившуюся кнопку в правой части этого поля. Откроется окно Построителя выражений.

3) Введите (сконструируйте) в этом окне следующий текст:

<= Date ()

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

К пункту 2 - перенос базы данных на SQL Server:

1) В меню Сервис выберите Служебные программы -> Мастер преобразования в формат SQL Server.

2) На первом экране Мастера преобразования установите переключатель в положение Создать базу данных и нажмите на кнопку Далее.

3) Оставьте для создаваемой базы данных предлагаемое по умолчанию имя ConstraintsSQL, убедитесь, что вы подключаетесь к локальному SQL Server при помощи доверительного соединения и нажмите Далее.

4) На следующем экране поместите в список экспортируемых таблиц таблицу MeasuresDateTime и нажмите Далее.

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

6) Убедитесь, что переключатель находится в положении "Не изменять приложение" и нажмите Далее, а затем Готово. После окончания работы мастера и формирования отчета закройте окно Access.

7) Чтобы просмотреть информацию о перенесенном ограничении целостности, откройте Enterprise Manager и подключитесь к вашему локальному серверу SQL Server. Затем раскройте контейнер Databases -> ConstratintsSQL -> Tables и щелкните правой кнопкой мыши по таблице MeasuresDateTime. В контекстном меню выберите Design Table и нажмите на кнопку Manage Constraints (справа). На вкладке Check Constraints будет показана информация ограничении целостности. Обратите внимание, как изменилось выражение - поскольку в TSQL нет функции Date().

Вау!! 😲 Ты еще не читал? Это зря!

• Согласованность данных
• Ссылочная целостность
• Модель данных

• Транзакции и целостность баз данных

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