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

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

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

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

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

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

Виды ограничений в базе данных

Существуют следующие виды ограничений в базе данных InterBase:

• первичный ключ - PRIMARY KEY;
• уникальный ключ - UNIQUE KEY;
• внешний ключ - FOREIGN KEY

Может включать автоматические триггеры ON UPDATE и ON DELETE;

• проверки - CHECK.

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

Для ограничений на основе нескольких полей синтаксис следующий:

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

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

Пример типичного ограничения

Фактически ограничения на основе одного поля являются частным сл\чаем ограничений на основе нескольких полей.

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

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

Создание ограничений

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

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

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

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

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

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

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

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

Синтаксис создания первичного и уникального ключа на основе единственного поля следующий:

Примеры первичных и уникальных ключей:

Синтаксис создания первичного и уникального ключей на основе нескольких полей:

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

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

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

Затем добавляем ключи. Сначала первичный:

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

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

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

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

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

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

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

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

Для этого создадим две таблицы для хранения накладной - таблицу TITLE для хранения заголовка и таблицу INVENTORY для хранения информации о товарах, входящих в накладную.

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

Теперь определим таблицу для хранения информации о товарах, входящих в накладную:

Давайте рассмотрим, какие поля входят в таблицу INVENTORY. Во-первых, это ID_INVENTORY - первичный ключ этой таблицы. Затем идет целочисленное поле FK_TITLE, которое служит для ссылки на идентификатор заголовка ID_TITLE в таблице заголовков накладных. Далее идут поля ProductName, Kolvo и Positio. описывающие наименование товара, его количество и позицию в накладной.

Для нашего примера важнее всего поле FK_TITLE. Если мы захотим вывести информацию о товарах определенной накладной, то нам следует воспользоваться следу ющиУ1 запросом, в котором параметр mas_ID_TITLE определяет идентификатор заголовка:

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

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

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

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

Часто при добавлении внешнего ключа возникает ошибка object is in use (объект используется) Дело в ю, что для создания внешнею ключа, необходимо открьпь базу данных в монопольном режиме - чтобы оиювременно не бьпо других пользователей Также нетьзя производить никаких обращений к модифицируемой таблице-это может вызвать object is in use

Здесь INVENTORY - имя таблицы, на которую накладывается ограничение внешнего ключа; fktitlel - имя внешнего ключа; FK_TITLE - поля, составляющие внешний ключ; TITLE - имя таблицы, предоставляющей значения (ссылочную ОСНОВУ) для внешнего ключа; ID_TITLE - поля первичного или уникального ключа в таблице TITLE которые являются ссылочной основой для внешнего ключа.

Полный синтаксис ограничения внешнего ключа (с возможностью создавать ограничения на основании нескольких полей) приведен ниже:

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

Чаще всего употребляются декларативная форма ограничения внешнего ключа, когда указывается набор полей (col [, col ...]), которые будут составлять ограничение; таблица other_table, которая содержит в полях [(other_col [, other_col ...]) список возможных значений для внешнего ключа.

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

Обратите внимание, что в этом определении опущены ключевые слова FOREIGN KEY, а также подразумевается, что в качестве внешнего ключа будет использоваться единственное поле - FK_TITLE.

А в следующем примере приведена более полная форма создания внешнего ключа одновременно с таблицей:

Использование NULL в полях внешнего ключа

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

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

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

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

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

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

Фактически для реализации этих действий создается системный триггер, который и выполняет определенные действия. В табл. 1.2 приведено описание происходящих действий при различных опциях (обратите внимание, что опции NO ACTION|CASCADE|SET DEFAULT|SET NULL не могут использоваться совместно в одном предложении ON XXX).

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

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

Ограничение CHECK

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

Синтаксисегодостаточнопрост:

Здесь constraint - имя ограничения; - условие поиска, в котором в качестве параметра может участвовать вставляемое/изменяемое значение. Если условие поиска выполняется, то вставка/изменение этого значения разрешаются, если нет - возникает ошибка.

Самый простой пример проверки:

Эта проверка устанавливает, больше ли нуля вставляемое/изменяемое значение поля ID, и в зависимости от результата позволяет вставить/изменить новое значение или возбудить исключение (см. главу "Расширенные возможности языка хранимых процедур InterBase" (ч. 1)).

Возможны и более сложные варианты проверок. Полный синтаксис условия поиска следующий:

Таким образом, CHECK предоставляет большой набор опций для проверки вставляемых/изменяемых значений. Необходимо помнить о следующих ограничениях в использовании СНЕК:

Данные в CHECK берутся только из текущей записи. Не следует брать данные для выражения в CHECK из других записей этой же таблицы - они могут быть изменены другими пользователями.

Поле может иметь только одно ограничение CHECK.

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

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

Удаление ограничений

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

где constraintname - имя ограничения, которое следует удалять. Если при создании ограничения было задано какое-то имя, то следует им воспользоваться, а если нет, то надо открыть какое-либо средство администрирования InterBase, поискать все связанные с ним ограничения и выяснить, какое системное имя сгенерировал InterBase для искомого ограничения.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Столбцы какого типа и размера будут составлять каждую из таблиц, какие требуется выбрать имена для столбцов таблиц?
• Какие столбцы могут содержать значение 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 .