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

Само понятие заявляемых ограничений целостности в SQL было унаследовано от реляционной модели и усложнялось вместе с развитием стандарта. В Oracle номенклатура ограничений целостности в целом соответствует SQL -92 (при том, что объем реализации не выдержан), но не доведена до уровня SQL :1999. Так, Oracle не позволяет завести ограничение целостности на уровне БД (с помощью служебного слова ASSERTION ) и сильно ограничен в формулировании условия проверки значений конструкцией CHECK тем, что не допускает обращения к данным базы.

Слово ASSERTION из стандарта SQL подсказывает еще один перевод (и понимание) integrity constraints , как "утвердительные ограничения целостности".

Заявляемые ограничения целостности в Oracle можно задавать на уровнях:

• отдельного поля строки в таблице;
• отдельной строки;
• пары таблиц.

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

Oracle позволяет формулировать подобные ограничения при создании таблицы командой CREATE TABLE , а для уже существующих таблиц их можно добавлять и отменять следующими командами:

• ALTER TABLE … MODIFY - добавление ограничений всех видов и снятие ограничения NOT NULL ;
• ALTER TABLE … ADD/DROP - добавление и снятие ограничений всех видов, кроме NOT NULL .

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

ALTER TABLE projx RENAME CONSTRAINT sys_c0011509 TO name_is_needed;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ALTER TABLE proj ADD UNIQUE (pname);

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

ALTER TABLE proj MODIFY (pname NOT NULL);

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

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

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

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

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

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

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

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

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

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

Любая операция над данными включает в себя селœекцию данных (select), то есть выделœение из всœей совокупности именно тех данных, над которыми должна быть выполнена требуемая операция, и действие над выбранными данными, ĸᴏᴛᴏᴩᴏᴇ определяет характер операции. Условие селœекции - ϶ᴛᴏ некоторый критерий отбора данных, в котором бывают использованы логическая позиция элемента данных, его значение и связи между данными.

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

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

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

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

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

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

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

За выполнением ограничений целостности следит СУБД в процессе своего функционирования. Она проверяет ограничения целостности каждый раз, когда они бывают нарушены (к примеру, при добавлении данных, при удалении данных и т.п.), и гарантирует их соблюдение. В случае если какая-либо команда нарушает ограничение целостности, она не будет выполнена и система выдаст соответствующее сообщение об ошибке. К примеру, в случае если задать в качестве ограничения правило ʼʼОстаток денежных средств на счёте не должна быть отрицательнымʼʼ, то при попытке снять со счёта денег больше, чем там есть, система выдаст сообщение об ошибке и не позволит выполнить эту операцию. Τᴀᴋᴎᴍ ᴏϬᴩᴀᴈᴏᴍ, ограничения целостности обеспечивают логическую непротиворечивость данных при переводе БД из одного состояния в другое.

Сегодня разработано много различных моделœей данных. Основные - ϶ᴛᴏ сетевая, иерархическая и реляционная модели.

Ограничения целостности - понятие и виды. Классификация и особенности категории "Ограничения целостности" 2017, 2018.

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

Манипулирование данными Примерами типичных операторов манипулирования иерархически организованными данными могут быть следующие: Найти указанное дерево БД (например, отдел 310); Перейти от одного дерева к другому; Перейти от одной записи к другой внутри дерева... .

- Ограничения целостности в модели сущность-связь

Здесь рассматриваются три типа ограничений: 1. Ограничения на допустимые значения в множестве значений. Атрибут отображает сущность из множества сущностей на множество значений. Допустимые значения определяются значениями в соответствующем множестве значений.... .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

= [ . . . ] = {UNIQUE | PRIMARY KEY | CHECK () | REFERENCES other_table [(other_col [, other_col ...])] }

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

= [< tconstraint> ...] = {{PRIMARY KEY | UNIQUE} (col [, col ...]) FOREIGN KEY (col [, col ...]) REFERENCES other_table [ (other_col [ , other_col ...]) ] | CHECK ()}

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

UNIQUE}

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

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

= {PRIMARY KEY | UNIQUE) (col [, col ...])

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE TABLE TITLE( IDJTITLE INTEGER NOT NULL Primary Key, DateNakl DATE, NumNakl INTEGER, NoteNakl VARCHAR(255));

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

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

CREATE TABLE INVENTORY( ID_INVENTORY INTEGER NOT NULL PRIMARY KEY, FK_TITLE INTEGER NOT NULL, ProductName VARCHAR (255), Kolvo DOUBLE PRECISION, Positio INTEGER);

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

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

SELECT * FROM INVENTORY II WHERE II.FK_TITLE=?mas_ID_TITLE

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

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

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

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

ALTER TABLE INVENTORY ADD CONSTRAINT fktitlel FOREIGN KEY(FK_TITLE) REFERENCES TITLE(ID_TITLE)

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

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

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

= FOREIGN KEY (col [, col }) REFERENCES other_table [ (other__col [ , other_col ...] } ]

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

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

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

CREATE TABLE Inventory2( ... FK_TABLE INTEGER NOT NULL CONSTRAINT fkinv REFERENCES TITLE(ID_TITLE) ...) ;

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

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

CREATE TABLE Inventory2( ... FK_TABLE INTEGER NOT NULL, CONSTRAINT fkinv FOREIGN KEY (FKJTABLE) REFERENCES TITLE(IDJTITLE) ...) ;

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

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

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

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

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

SET NULL}]

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

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

ALTER TABLE INVENTORY ADD CONSTRAINT fkautodel FOREIGN KEY (FK_TITLE) REFERENCES TITLE(ID_TITLE) ON DELETE CASCADE

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

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

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

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

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

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

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

= CHECK ( )}

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

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

create table checktst( ID integer CHECK(ID>0));

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

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

= { { | ()} | BETWEEN AND | LIKE | IN ( [ , ...] | ) | IS NULL | { {= | < | >} | >= | <=} {ALL | SOME | ANY} () |EXISTS () | SINGULAR () | CONTAINING | STARTING | () | NOT | OR | AND }

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

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

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

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

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

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

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

ALTER TABLE cablename DROP CONSTRAINT constraintname

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Таблицы с ограничениями в стандарте языка

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

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

Большая часть перечисленных ограничений задается в операторах CREATE TABLE и ALTER TABLE .

Создание таблицы

В стандарте SQL дано несколько вариантов определения оператора создания таблицы , однако его базовый формат имеет следующий вид:

<определение_таблицы> ::= CREATE TABLE имя_таблицы {(имя_столбца тип_данных [ NOT NULL ][ UNIQUE] [ CHECK (<условие_выбора>)][,...n]} ) {)} ) REFERENCES имя_род_таблицы [(имя_столбца_род_таблицы [,...n])], {[,...n]})

Ограничения

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

Обязательные данные

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

Требования конкретного предприятия

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

Ограничения для доменов полей

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

Целостность сущностей

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

Ссылочная целостность

Внешние ключи представляют собой столбцы или наборы столбцов, предназначенные для связывания каждой из строк дочерней таблицы, содержащей этот внешний ключ , со строкой родительской таблицы, содержащей соответствующее значение потенциального ключа. Стандарт SQL предусматривает механизм определения внешних ключей с помощью предложения FOREIGN KEY , а фраза REFERENCES определяет имя родительской таблицы, т.е. таблицы, где находится соответствующий потенциальный ключ. При использовании этого предложения система отклонит выполнение любых операторов INSERT или UPDATE , с помощью которых будет предпринята попытка создать в дочерней таблице значение внешнего ключа , не соответствующее одному из уже существующих значений потенциального ключа родительской таблицы. Когда действия системы выполняются при поступлении операторов UPDATE и DELETE , содержащих попытку обновить или удалить значение потенциального ключа в родительской таблице, которому соответствует одна или более строк дочерней таблицы, то они зависят от правил поддержки ссылочной целостности , указанных во фразах ON UPDATE и ON DELETE предложения FOREIGN KEY . Если пользователь предпринимает попытку удалить из родительской таблицы строку, на которую ссылается одна или более строк дочерней таблицы, язык SQL предоставляет следующие возможности:

• CASCADE - выполняется удаление строки из родительской таблицы, сопровождающееся автоматическим удалением всех ссылающихся на нее строк дочерней таблицы;
• SET NULL - выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы записывается значение NULL ;
• SET DEFAULT - выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносится значение, принимаемое по умолчанию;
• NO ACTION - операция удаления строки из родительской таблицы отменяется. Именно это значение используется по умолчанию в тех случаях, когда в описании внешнего ключа фраза ON DELETE опущена.

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

Определитель MATCH позволяет уточнить способ обработки значения NULL во внешнем ключе .

При определении таблицы предложение FOREIGN KEY может указываться произвольное количество раз.

В операторе CREATE TABLE используется необязательная фраза DEFAULT , которая предназначена для задания принимаемого по умолчанию значения, когда в операторе INSERT значение в данном столбце будет отсутствовать.

Фраза CONSTRAINT позволяет задать имя ограничению, что позволит впоследствии отменить то или иное ограничение с помощью оператора ALTER TABLE .

Изменение и удаление таблицы

Для внесения изменений в уже созданные таблицы стандартом SQL предусмотрен оператор ALTER TABLE , предназначенный для выполнения следующих действий:

• добавление в таблицу нового столбца;
• удаление столбца из таблицы;
• добавление в определение таблицы нового ограничения;
• удаление из определения таблицы существующего ограничения;
• задание для столбца значения по умолчанию;
• отмена для столбца значения по умолчанию.

Оператор изменения таблицы имеет следующий обобщенный формат:

<изменение_таблицы> ::= ALTER TABLE имя_таблицы имя_столбца тип_данных [ NOT NULL ] [ CHECK (<условие_выбора>)]] имя_столбца ] ] [{PRIMARY KEY (имя_столбца [,...n]) |)} |) REFERENCES имя_род_таблицы [(имя_столбца_род_таблицы [,...n])], [ MATCH {PARTIAL | FULL} |[,...n]}] ] SET DEFAULT <значение>] DROP DEFAULT]

Здесь параметры имеют то же самое назначение, что и в определении оператора CREATE TABLE .

Оператор ALTER TABLE реализован не во всех диалектах языка SQL. В некоторых диалектах он поддерживается, однако не позволяет удалять из таблицы уже существующие столбцы.

Для удаления таблицы используется команда DROP TABLE .

Таблицы в среде MS SQL Server

Создание таблицы

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

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

Ограничение уникального ключа (UNIQUE)

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

Ограничение на значение (NOT NULL)

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

Ограничение проверочное (CHECK) и правила

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

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

Допускается применение нескольких ограничений CHECK к одному и тому же столбцу. В этом случае они будут применимы в той последовательности, в которой происходило их создание. Возможно применение одного и того же ограничения к разным столбцам и использование в логических выражениях значений других столбцов. Указание параметра NOT FOR REPLICATION предписывает не выполнять проверочных действий, если они выполняются подсистемой репликации.

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

Правило может быть создано командой:

CREATE RULE имя_правила AS выражение

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

sp_bindrule [@rulename=] "rule" [@objname=] "object_name" [,[@futureonly=["futureonly_flag"]

Чтобы отменить правила , следует выполнить следующую процедуру:

sp_unbindrule [@objname=] "object_name" [,[@futureonly=["futureonly_flag"]

Удаление правила производится командой

DROP RULE {имя_правила} [,...n].

Ограничение по умолчанию (DEFAULT)

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

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

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

Дополнительным механизмом использования значений по умолчанию являются объекты базы данных, созданные командой:

CREATE DEFAULT имя_умолчания AS константа

Умолчание связывается с тем или иным столбцом какой-либо таблицы с помощью процедуры:

sp_bindefault [@defname=] "default", [@objname=] "object_name" [,[@futureonly=] "futureonly_flag"],

может быть представлен как

"имя_таблицы.имя_столбца"

Удаление ограничения по умолчанию выполняется командой

DROP DEFAULT {имя_умолчания} [,...n]

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

sp_unbindefault [@objname=] "object_name" [,[@futureonly=] "futureonly_flag"]

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

Ключевые слова CLUSTERED и NONCLUSTERED позволяют создать для столбца кластерный или некластерный индекс . Для ограничения PRIMARY KEY по умолчанию создается кластерный индекс , а для ограничения UNIQUE - некластерный . В каждой таблице может быть создан лишь один кластерный индекс , отличительной особенностью которого является то, что в соответствии с ним изменяется физический порядок строк в таблице. ASC и DESC определяют метод упорядочения данных в индексе.

С помощью параметра WITH FILLFACTOR=фактор_заполнения задается степень заполнения индексных страниц при создании индекса. Значение фактора заполнения указывается в процентах и может изменяться в промежутке от 0 до 100.

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

Изменение таблицы

Изменения в таблицу можно внести командой:

<изменение_таблицы> ::= ALTER TABLE имя_таблицы {)] [ NULL | NOT NULL ] | {ADD | DROP } ROWGUIDCOL }] | ADD { [<определение_столбца>] | имя_столбца AS выражение } [,...n] | ADD { <ограничение-таблицы> } [,...n] | DROP { имя_ограничения | COLUMN имя_столбца}[,...n] | {CHECK | NOCHECK } CONSTRAINT {ALL | имя_ограничения[,...n]} | {ENABLE | DISABLE } TRIGGER {ALL | имя_триггера [,...n]}}

В дополнение к уже названным параметрам определим параметр {ENABLE | DISABLE } TRIGGER ALL_ , предписывающий задействовать или отключить конкретный триггер или все триггера, связанные с таблицей.

Удаление таблицы

Удаление таблицы выполняется командой:

DROP TABLE имя_таблицы

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

CREATE TABLE Товар (КодТовара INT IDENTITY(1,1) PRIMARY KEY, Название VARCHAR(50) NOT NULL UNIQUE, Цена MONEY NOT NULL, Тип VARCHAR(50) NOT NULL, Сорт VARCHAR(50) NOT NULL CHECK(сорт in("первый","второй","третий")), Город VARCHAR(50) NOT NULL, Остаток INT CHECK(остаток>=0)) Пример 9.1. Создание родительской таблицы Товар с ограничениями.

CREATE TABLE Клиент (КодКлиента INT IDENTITY(1,1) PRIMARY KEY, Фирма VARCHAR(50) NOT NULL, Фамилия VARCHAR(50) NOT NULL, Город VARCHAR(50) NOT NULL, Телефон CHAR(10) NOT NULL CHECK(Телефон LIKE "--")) Пример 9.2. Создание родительской таблицы Клиент с ограничениями.

CREATE TABLE Сделка (КодСделки INT IDENTITY(1,1) PRIMARY KEY, КодТовара INT NOT NULL, КодКлиента INT NOT NULL, Количество INT NOT NULL DEFAULT 0, Дата DATETIME NOT NULL DEFAULT GETDATE(), CONSTRAINT fk_Товар FOREIGN KEY(КодТовара) REFERENCES Товар, CONSTRAINT fk_Клиент FOREIGN KEY(КодКлиента) REFERENCES Клиент) Пример 9.3. Создание дочерней таблицы Сделка с ограничениями.

Пусть создана таблица без ограничений:

CREATE TABLE Товар (КодТовара INT, Название VARCHAR(20), Тип VARCHAR(20), Дата DATETIME, Цена MONEY, Остаток INT)

Рассмотрим примеры внесения в таблицу всевозможных ограничений.

Пример 9.9. Поле КодТовара необходимо сделать первичным ключом. Выполнение следующей команды будет отвергнуто, поскольку поле КодТовара допускает внесение значений NULL .

ALTER TABLE Товар ADD CONSTRAINT pk1 PRIMARY KEY(КодТовара) Пример 9.9. Поле КодТовара необходимо сделать первичным ключом.

Сначала нужно изменить объявление столбца КодТовара , запретив внесение значений NULL :

ALTER TABLE Товар ALTER COLUMN КодТовара INT NOT NULL

И только потом создать ограничение первичного ключа:

Пример 9.10. Удалить столбец целого типа и добавить столбец-счетчик.

CREATE DEFAULT df1 AS 0 sp_bindefault "df1", "Товар.Остаток" CREATE DEFAULT df2 AS GETDATE() sp_bindefault "df2", "Товар.Дата" Пример 9.14. Создание и добавление умолчания столбцу.

Пример 9.15. Создать правило и добавить правило столбцу.

CREATE RULE r1 AS @m IN ("мебель","бытовая химия","косметика") sp_bindrule "r1","Товар.Тип" Пример 9.15. Создание и добавление правила столбцу.