Целостность базы данных означает что она содержит

Урок 26
Информационные системы. Таблицы
§ 30. Информационные системы
§ 31. Таблицы

Содержание урока

§ 30. Информационные системы

§ 31. Таблицы

Целостность

§ 31. Таблицы

Целостность

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

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

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

Используя дополнительные источники, выясните, от каких слов образовано сокращение RAID и как переводится это выражение на русский язык. Найдите два варианта ответа на этот вопрос.

Теперь представьте себе, что в базе данных отдела кадров по ошибке у работника указан 1698 год рождения, а в поле Зарплата введено отрицательное число. В этих случаях нарушается логическая целостность БД, т. е. непротиворечивость данных. Чтобы этого не произошло, вводят ограничения на допустимые значения полей (контроль данных):

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

Заметим, что целостность БД не гарантирует достоверность (истинность) данных, а только означает, что выполнены все установленные ограничения на эти данные и исключены явные противоречия.

Определите, какие нарушения логической целостности допущены в таблице на рис. 6.7. Сколько ошибок вам удалось найти?

Следующая страница Выводы

Cкачать материалы урока

Источник

Целостность базы данных

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

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

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

Итак, БД может быть целостной, но не достоверной. Возможно и обратное: БД может быть достоверной, но не целостной. Последнее имеет место, если правила (ограничения целостности) заданы неверно.

Источник

Целостность базы данных означает что она содержит

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

Пример нарушения целостности базы

Для иллюстрации возможного нарушения целостности базы данных рассмотрим следующий пример:

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

Понятие транзакции

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

Замечание. Некоторые системы (например, Visual FoxPro), требуют подать явную команду BEGIN TRANSACTION для того, чтобы начать новую транзакцию.

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

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

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

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

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

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

Примерами ограничений целостности могут служить следующие утверждения:

Пример 2. Возраст сотрудника не может быть меньше 18 и больше 65 лет.

Пример 3. Каждый сотрудник имеет уникальный табельный номер.

Пример 4. Сотрудник обязан числиться в одном отделе.

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

Как видно из этих примеров, некоторые из ограничений целостности являются ограничениями реляционной модели данных (см. гл. 3). Пример 3 представляет ограничение, реализующее целостность сущности. Пример 4 представляет ограничение, реализующее ссылочную целостность. Другие ограничения являются достаточно произвольными утверждениями (примеры 2 и 5). Любое ограничение целостности является семантическим понятием, т.е. появляется как следствие определенных свойств объектов предметной области и/или их взаимосвязей.

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

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

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

Например, если система знает, что в поле «Возраст_Сотрудника» должны быть целые числа в диапазоне от 18 до 65, то система отвергает попытку ввести значение возраста 66. При этом может генерироваться какое-нибудь сообщение для пользователя.

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

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

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

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

Например, следующий оператор создает таблицу PERSON и определяет для нее некоторые ограничения целостности:

Средства декларативной поддержки ограничений описаны в стандарте SQL и более подробно рассматриваются ниже.

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

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

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

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

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

Если система не поддерживает ни декларативную поддержку ссылочной целостности, ни триггеры (как, например, FoxPro 2.5), то программный код, следящий за корректностью базы данных, приходится размещать в пользовательском приложении (такой ведь код все равно необходим!). Это сильно затрудняет разработку программ и не защищает от попыток пользователей напрямую внести некорректные данные в базу данных. Особенно сложно становится в том случае, когда имеется сложная база данных и множество различных приложений, работающих с ней (например, к базе данных торгового предприятия может обращаться несколько приложений, таких как «Складской учет», «Прием заказов», «Главный бухгалтер» и т.п.). Каждое из таких приложений должно содержать один и тот же код, отвечающий за поддержание целостности базы данных. Особенно весело становится разработчику, когда необходимо внести изменения в логику поддержания целостности. Приходится заменять во всех программах одни и те же места, перекомпилировать все приложения и распространять по рабочим местам новые версии.

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

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

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

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

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

Например, ограничением домена «Возраст сотрудника» может быть условие «Возраст сотрудника не менее 18 и не более 65».

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

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

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

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

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

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

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

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

Пример 7. Для отношения «Сотрудники» можно сформулировать следующее ограничение: если атрибут «Должность» принимает значение «Директор», то атрибут «Зарплата» содержит значение не менее 1000$.

Это ограничение связывает два атрибута одного кортежа.

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

Таблица 3 Атрибуты товара

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

Решение 1. Пусть в отношении решено хранить только базовые атрибуты.

Решение 2. Предположим, что в отношении решено хранить все атрибуты, в том числе и вычислимые.

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

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

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

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

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

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

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

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

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

Замечание. Для того чтобы ввести в действие (объявить) это ограничение, необходимо, чтобы в отношение уже были вставлены некоторые кортежи.

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

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

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

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

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

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

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

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

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

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

Пример 13. Ограничение целостности ссылок (см. гл. 3), задаваемое внешним ключом отношения, является ограничением базы данных.

Пример 14. Ограничение на таблицы DEPART и PERSON из примера 1 является отношением базы данных, т.к. оно связывает данные, размещенные в различных таблицах.

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

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

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

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

Общие принципы реализации ограничений средствами SQL

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

Как видно, действия, исполняемые по ссылке, фактически являются встроенными в СУБД триггерами. Действия типа CASCADE, SET NULL и SET DEFAULT являются компенсирующими операциями, вызывающимися при попытке нарушить ссылочную целостность.

Синтаксис ограничений стандарта SQL

Понятие ограничения используется во многих операторах определения данных (DDL).

Ограничения таблицы ::=
[CONSTRAINT Имя ограничения]
<
<PRIMARY KEY (Имя столбца. )>
| <UNIQUE (Имя столбца. )>
| <FOREIGN KEY (Имя столбца. ) REFERENCES Имя таблицы [(Имя столбца. )] [Ссылочная спецификация]>
| <Ограничение check >
>
[Атрибуты ограничения]

Ограничения столбца::=
[CONSTRAINT Имя ограничения]
<
<NOT NULL>
| <PRIMARY KEY>
| <UNIQUE>
| <REFERENCES Имя таблицы [(Имя столбца)] [Ссылочная спецификация]>
| <Ограничение check >
>
[Атрибуты ограничения]

Ссылочная спецификация::=
[MATCH <FULL | PARTIAL>]
[ON UPDATE <CASCADE | SET NULL | SET DEFAULT | NO ACTION>]
[ON DELETE <CASCADE | SET NULL | SET DEFAULT | NO ACTION>]

Атрибуты ограничения::=
<DEFERRABLE [INITIALLY DEFERRED | INITIALLY IMMEDIATE]>
| <NOT DEFERRABLE>

Пример 15. Пример ограничения типа CHECK:

CHECK (Salespeaple.Salary IS NOT NULL) OR (Salespeaple.Commission IS NOT NULL)

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

Пример 16. Еще пример ограничения типа CHECK:

CHECK EXIST(SELECT * FROM Salespeaple)

Данное ограничение утверждает, что список продавцов не может быть пустым.

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

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

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

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

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

Ограничения FOREIGN KEY и REFERENCES. Ограничение FOREIGN KEY… REFERENCES… для таблицы и ограничение REFERENCES… для столбца определяют внешний ключ таблицы. Ограничение REFERENCES… для столбца определяет простой внешний ключ, т.е. ключ, состоящий из одной колонки. Ограничение FOREIGN KEY… REFERENCES… для таблицы может определять как простой, так и сложный внешний ключ, т.е. ключ, состоящий из нескольких колонок таблицы. Столбец или группа столбцов таблицы, на которую ссылается внешний ключ, должна иметь ограничения PRIMARY KEY или UNIQUE. Столбцы, на которые ссылается внешний ключ, должны иметь тот же тип данных, что и столбцы, входящие в состав внешнего ключа. Таблица может иметь ссылку на себя. Ограничение внешнего ключа нарушается, если значения, присутствующие во внешнем ключе, не совпадают со значениями соответствующего ключа родительской таблицы ни для одной строки из родительской таблицы. Операции, приводящие к нарушению ограничения внешнего ключа, отвергаются. Как должны совпадать значения внешнего ключа и ключа родительской таблицы, а также, какие действия необходимо выполнить при изменениях ключей в родительской таблице, описаны ниже в ссылочной спецификации.

Ограничение NOT NULL. Ограничение NOT NULL столбца не допускает появления в столбце NULL-значений.

Ссылочная спецификация. Ссылочная спецификация определяет характеристики внешнего ключа таблицы.

Пример 17. Пусть имеется две таблицы:

Таблица 4 таблица A (Родительская)

Таблица 5 Таблица B (Дочерняя)

Таблица A имеет первичный ключ (X, Y). Таблица B имеет первичный ключ Z, и внешний ключ (X, Y), ссылающийся на первичный ключ таблицы A. Различные варианты совпадения строк дочерней таблицы B со строками родительской таблицы A приведены ниже:

Предложение MATCH игнорируется, если все столбцы внешнего ключа имеют ограничения NOT NULL.

Предложения ON UPDATE и ON DELETE. Предложения ON UPDATE и ON DELETE определяют действия, исполняемые по ссылке. Действия, исполняемые по ссылке, в основном описаны выше в этой главе. Сложности в понимании того, как выполняются эти действия, возникают если установлено MATCH PARTIAL и колонки, входящие в состав внешнего ключа, допускают NULL-значения. Подробно эти действия с учетом возможных сложностей описаны в [9].

Атрибуты ограничения. Атрибуты ограничения определяют, в какой момент проверяются ограничения. Ограничение может быть определено как NOT DEFERRABLE (неоткладываемое) или DEFERRABLE (откладываемое). Если атрибуты ограничения не указаны, то по умолчанию принимается NOT DEFERRABLE.

Если ограничение определено как NOT DEFERRABLE (неоткладываемое), то ограничение всегда проверяется сразу после выполнения каждого оператора INSERT, UPDATE или DELETE, которые могут привести к нарушению ограничения.

Синтаксис операторов SQL, использующих ограничения

CREATE DOMAIN Имя домена [AS] Тип данных

[DEFAULT Значение по умолчанию]
[Имя ограничения] Ограничение check [Атрибуты ограничения]

Этот оператор задает домен, на основе которого можно определять колонки таблиц. Т.к. имя колонки, которая будет основана на этом домене заранее неизвестно, то в ограничении CHECK домена для ссылки на значение этого домена используется ключевое слово VALUE. В конкретной таблице СУБД заменит слово VALUE на имя колонки таблицы.

Пример 18. Приведенный ниже оператор создает домен Salary на основе целочисленного типа данных, причем значения из этого домена не могут принимать неположительные значения (но могут принимать значение NULL!). По умолчанию это ограничение проверяется немедленно, но может быть и отложенным:

ALTER DOMAIN Имя домена
<SET DEFAULT Значение по умолчанию>
| <DROP DEFAULT>
| <ADD [Имя ограничения] Ограничение check [Атрибуты ограничения]>
| <DROP CONSTRAINT Имя ограничения>

Этот оператор изменяет имеющийся домен. Стандарт запрещает вносить несколько изменений одной командой ALTER DOMAIN. Т.е. если требуется удалить ограничение CHECK и добавить значение по умолчанию, то придется выполнить два оператора ALTER DOMAIN.

DROP DOMAIN Имя домена CASCADE | RESTRICT

CREATE TABLE Имя таблицы
( <Определение столбца | [Ограничение таблицы]>. )

Определение столбца::=
Имя столбца <Имя домена | Тип данных [Размер]>
[Ограничение столбца…]
[DEFAULT Значение по умолчанию]

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

ALTER TABLE Имя таблицы
<ADD [COLUMN] Определение столбца>
| <ALTER [COLUMN] Имя столбца <SET DEFAULT Значение по умолчанию | DROP DEFAULT>>
| <DROP [COLUMN] Имя столбца RESTRICT | CASCADE>
| <ADD Ограничение таблицы>
| <DROP CONSTRAINT Имя ограничения RESTRICT | CASCADE>

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

DROP TABLE Имя таблицы CASCADE | RESTRICT

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

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

Если указан параметр CASCADE, то удаляются также и все объекты, ссылающиеся на эту таблицу.

CREATE ASSERTION Имя утверждения
Ограничение check
[Атрибуты ограничения]

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

DROP ASSERTION Имя утверждения

Этот оператор позволяет удалять имеющееся утверждение.

Этот оператор фиксирует транзакцию. При этом проверяются все отложенные до конца транзакции ограничения. Если одно из ограничений не выполняется, то транзакция откатывается.

SET CONSTRAINT <Имя ограничения. | ALL>
<DEFERRED | IMMEDIATE>

Этот оператор позволяет во время выполнения транзакции менять момент проверки всех (ALL) или некоторых ограничений. Этот оператор действует только на ограничения, определенные как DEFERRABLE (потенциально откладываемые). Если ограничение A находилось в состоянии IMMEDIATE (немедленно проверяемое), то оператор SET CONSTRAINT A DEFERRED переводит его в состояние DEFERRED (с отложенной проверкой) и тогда все операции, потенциально могущие нарушить это ограничение, будут выполняться без проверки. Проверка будет произведена в конце транзакции или в момент подачи команды SET CONSTRAINT A IMMEDIATE.

Выводы

База данных находится в согласованном состоянии, если для этого состояния выполнены все ограничения целостности.

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

Источник