Ограничения базы данных в Firebird и InterBase
NOTICE: Этот документ является главой из книги “Мир InterBase”, написанной Алексеем Ковязиным и Сергом Востриковым.
Эта глава посвящена ограничениям баз данных InterBase и Firebird. Ограничения базы данных - это правила, определяющие взаимосвязи между таблицами, которые могут проверять и изменять данные в базе данных. Эти правила реализованы в виде специальных объектов базы данных. Основное преимущество использования ограничений заключается в возможности реализации проверки данных и части бизнес-логики приложения на уровне базы данных, то есть централизовать и упростить её, что делает разработку приложений баз данных проще и надёжнее.
Начинающие разработчики часто пренебрегают использованием ограничений базы данных, считая, что они мешают творческой работе. Однако на самом деле такое мнение формируется из-за недостаточного знания теории и практики проектирования баз данных.
В то же время наиболее опытные проектировщики решаются отказаться от использования некоторых типов ограничений, благодаря чему их приложения выигрывают в скорости. Опыт экспертов-проектировщиков позволяет им очень хорошо понимать работу сервера и точно предсказывать его поведение в сложных случаях, поэтому начинающим программистам InterBase лучше не прибегать к подобным действиям опытных коллег.
В рамках этой книги мы не рассматриваем проектирование баз данных, поэтому для получения дополнительной информации по этому вопросу обратитесь к списку литературы в конце книги. Здесь мы рассмотрим только все типы ограничений в базе данных InterBase и рассмотрим примеры их применения.
Типы ограничений в базе данных
В базе данных InterBase существуют следующие типы ограничений:
- PRIMARY KEY;
- UNIQUE KEY;
- FOREIGN KEY
- могут включать автоматические триггеры - ON UPDATE и ON DELETE;
- CHECK
В предыдущих главах мы упоминали некоторые из этих ограничений, поскольку это было необходимо для логического изложения материала, но теперь мы рассмотрим их синтаксис, применение и реализацию более подробно. Ограничения базы данных бывают двух типов - основанные на одном поле и основанные на нескольких полях таблицы. Синтаксис обоих типов ограничений приведён ниже.
= [CONSTRAINT constraint]
[ …]
= {UNIQUE | PRIMARY KEY
| CHECK ( )
| REFERENCES other_table [( other_col [, other_col …])]
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
}
Синтаксис ограничений, основанных на нескольких полях, следующий:
= [CONSTRAINT constraint]
[< tconstraint> …]
= {{PRIMARY KEY | UNIQUE} ( col [, col …])
| FOREIGN KEY ( col [, col …]) REFERENCES other_table[( other_col [, other_col …])]
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
| CHECK ( )}
Разница в синтаксисе между ограничениями, основанными на одном поле и на нескольких полях, очевидна - в последнем случае мы можем указать несколько полей, включённых в ограничение. В случае ограничения, основанного на одном поле, все описанные параметры относятся только к текущему полю. Конечно, эти два типа ограничений имеют разный способ применения: ограничения, основанные на одном поле, просто добавляются к определению требуемого поля, а ограничения, основанные на нескольких полях, указываются после запятой в общем определении таблицы. Подробные примеры приведены в следующих частях этой главы.
Пример типичного ограничения
На самом деле ограничения, основанные на одном поле, являются частным случаем ограничений, основанных на нескольких полях.
Пример создания ограничения первичного ключа с использованием этих двух различных подходов приведён ниже. Давайте создадим таблицу, содержащую только одно поле, и установим для неё ограничение первичного ключа.
Вот пример первичного ключа с использованием синтаксиса ограничения, основанного на одном поле:
CREATE TABLE test1( ID_PK INTEGER CONSTRAINT pktest NOT NULL PRIMARY KEY); В этом примере для поля ID_PK создаётся первичный ключ с именем pktest. В результате мы получаем довольно компактное описание в одну строку. Для той же цели мы можем использовать синтаксис ограничений, основанных на нескольких полях: CREATE TABLE test2( ID_PK INTEGER NOT NULL, CONSTRAINT pktst PRIMARY KEY (ID_PK));
Создание ограничений
Рассмотрим создание ограничений более подробно. Первым в описании общего синтаксиса ограничений идёт параметр [CONSTRAINT constraint]. Как вы можете видеть, этот параметр заключён в квадратные скобки, то есть является необязательным.
Используя этот параметр, вы можете задать имя создаваемому ограничению как в случае применения синтаксиса ограничений, основанных на одном поле, так и в случае ограничений, основанных на нескольких полях. Если вы не указали имя для ограничения, InterBase автоматически сгенерирует его. Тем не менее, лучше задать имя создаваемому ограничению, чтобы улучшить читаемость схемы базы данных и упростить управление ограничениями в дальнейшем.
Задав имя ограничению, следует определить его тип. Рассмотрим различные типы ограничений в том порядке, в котором они указаны в описании общего синтаксиса ограничений.
Первичные и уникальные ключи
Первичные ключи являются одним из основных типов ограничений базы данных. Они применяются для однозначной идентификации записей в таблице. Предположим, что мы храним список людей в базе данных. Вполне возможно, что будут два (или более) человека с одинаковыми фамилией, именем и отчеством. Как мы можем отличить одного человека от другого (конечно, вопрос заключается в отличии одного человека от другого согласно информации, хранящейся в базе данных)?
В этом случае «человек» представлен одной записью в таблице, поэтому мы можем задать более общий вопрос - как мы можем отличить одну запись в (любой) таблице от другой записи в той же таблице. Для этой цели используются ограничения - первичные ключи. Первичный ключ представляет собой одно или несколько полей в таблице, комбинация которых уникальна для каждой записи. В таблице не существует повторяющихся значений первичного ключа.
Уникальные ключи выполняют ту же функцию - они также служат для однозначной идентификации записей в таблице. Разница между первичными и уникальными ключами заключается в том, что в таблице может быть только один первичный ключ, а уникальных ключей может быть несколько. Отмечено, что и первичный, и уникальный ключ могут использоваться в качестве основы для ссылок внешних ключей (см. далее).
Формальное описание понятий первичного и уникального ключей, а также другие важные определения можно найти в приложении «Глоссарий» в конце книги. Синтаксис создания первичного и уникального ключа на основе одного поля следующий:
< pkukconstraint > = [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE}
Примеры первичного и уникального ключей:
CREATE TABLE pkuk( pk NUMERIC(15,0) NOT NULL PRIMARY KEY, /*первичный ключ*/
uk1 VARCHAR(50) NOT NULL UNIQUE,/*уникальный ключ */
uk2 INTEGER NOT NULL UNIQUE /\* ещё один уникальный ключ */);
Синтаксис создания первичного и уникального ключей на основе нескольких полей:
= [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])
Такой синтаксис позволяет создавать ключи на основе комбинации полей. Вот примеры создания первичного и уникального ключей из нескольких полей:
CREATE TABLE pkuk2( Number1 INTEGER NOT NULL, Name1 VARCHAR(50) NOT NULL, Kol INTEGER NOT NULL, Stoim NUMERIC(15,4) NOT NULL, CONSTRAINT pkt PRIMARY KEY (Number1, Name1), /*первичный ключ pkt на основе двух полей*/ CONSTRAINT ukt1 UNIQUE (kol, Stoim)); /*уникальный ключ ukt1 на основе двух полей*/
Обратите внимание, что все поля, включённые в первичные и уникальные ключи, должны быть объявлены как NOT NULL, поскольку эти ключи не могут иметь неопределённое значение. Помимо создания ограничений первичного и уникального ключей при создании таблицы, существует возможность добавлять ограничения к уже существующей таблице. В этом случае используется оператор DDL: ALTER TABLE. Синтаксис добавления ограничений первичного или уникального ключа к существующей таблице аналогичен описанному выше:
ALTER TABLE tablename ADD [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])
Рассмотрим пример создания первичного и уникального ключа с помощью ALTER TABLE:
CREATE TABLE pkalter( ID1 INTEGER NOT NULL, ID2 INTEGER NOT NULL, UID VARCHAR(24));
Затем добавляем ключи. Сначала первичный:
ALTER TABLE pkalter ADD CONSTRAINT pkal1 PRIMARY KEY (id1, id2);
Затем уникальный: ALTER TABLE pkalter ADD CONSTRAINT ukal UNIQUE (uid);
Следует отметить, что только владелец этой таблицы или системный администратор SYSDBA (подробнее о владельцах и пользователе SYSDBA см. главу «Безопасность в InterBase: пользователи, их функции и права» - часть 4) может выполнять добавление (а также удаление) первичных и уникальных ключей к таблице.
Внешние ключи
Следующее ограничение, часто используемое в базах данных InterBase, - это ограничение внешнего ключа. Это очень мощный инструмент для обеспечения ссылочной целостности в базе данных, который позволяет не только контролировать наличие корректных ссылок в базе данных, но и управлять этими ссылками автоматически!
Смысл создания внешнего ключа заключается в следующем: если две таблицы служат для хранения взаимосвязанной информации, необходимо гарантировать, что эта взаимосвязь всегда будет корректной. Например, документ «накладная», содержащий общую шапку (дата, номер накладной и т.д.) и набор детальных записей (описание товаров, количество и т.д.).
Для хранения такого документа в базе данных создаются две таблицы - одна для хранения шапок накладных, а вторая - для хранения содержимого накладной - записей о товарах и их количестве. Такие таблицы называются главной и подчиненной, или таблицей-мастером и таблицей-деталью.
По здравому смыслу, содержимое накладной не может существовать без наличия её шапки. Другими словами, мы не можем вставить запись о товаре, если не создали шапку накладной, и не можем удалить запись шапки, если существуют записи о товарах. Для реализации такого поведения таблица шапки и таблица деталей связываются с помощью ограничения внешнего ключа.
Рассмотрим смысл установки ограничений внешнего ключа на примере таблиц, содержащих информацию о накладных. Для этой цели создадим две таблицы для хранения накладной - таблицу TITLE для хранения шапки и таблицу INVENTORY для хранения информации о товарах, включенных в накладную.
CREATE TABLE TITLE( ID_TITLE 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 является наиболее важным для нашего примера. Если мы хотим вывести информацию о товарах определенной накладной, мы должны использовать следующий запрос, в котором параметр mas_ID_TITLE определяет идентификатор шапки:
SELECT * FROM INVENTORY I1 WHERE I1.FK_TITLE=?mas_ID_TITLE
Фактически, в описанной ситуации ничто не мешает заполнить таблицу INVENTORY записями, ссылающимися на несуществующие записи в таблице TITLE. Кроме того, ничто не мешает удалить шапку уже существующей накладной, из-за чего записи о товарах могут стать «бесхозными». Сервер не запретит выполнять все эти вставки и удаления. Таким образом, контроль над целостностью данных в базе данных полностью возлагается на клиентское приложение. Однако вы знаете, что с одной базой данных могут работать несколько приложений, разработанных, возможно, разными программистами, что может привести к различной интерпретации данных и ошибкам. Следовательно, необходимо установить явное ограничение, чтобы в таблицу INVENTORY можно было помещать только такие записи о товарах, которые имеют корректную ссылку на шапку накладной. Это, по сути, и есть ограничение внешнего ключа, которое позволяет вставлять в поля, включенные в ограничение, только те значения, которые присутствуют в другой таблице.
Такое ограничение может быть создано с помощью внешнего ключа. Для данного примера мы должны установить ограничение внешнего ключа для поля FK_TITLE и связать его с первичным ключом ID_TITLE в таблице TITLE. Мы можем добавить внешний ключ к уже существующей таблице следующей командой:
ALTER TABLE INVENTORY ADD CONSTRAINT fktitle1 FOREIGN KEY(FK_TITLE) REFERENCES TITLE(ID_TITLE)
Часто при добавлении внешнего ключа появляется ошибка - object is in use. Дело в том, что для создания внешнего ключа необходимо открыть базу данных в монопольном режиме - чтобы в это время не было других пользователей. Также не следует обращаться к изменяемой таблице - это может вызвать ошибку object is in use.
Здесь INVENTORY - это имя таблицы, для которой устанавливается ограничение внешнего ключа; fktitle1 - имя внешнего ключа; FK_TITLE - поля, составляющие внешний ключ; TITLE - имя таблицы, предоставляющей значения (основу ссылки) для внешнего ключа; ID_TITLE - поля первичного или уникального ключа в таблице TITLE, которые служат основой ссылки для внешнего ключа. Полный синтаксис ограничения внешнего ключа (с возможностью создания ограничений на основе нескольких полей) приведен ниже:
= [CONSTRAINT constraint] FOREIGN KEY ( col [, col …]) REFERENCES other_table [( other_col [, other_col …])] [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
Как видите, определения содержат большой набор опций. Для начала рассмотрим базовое определение внешнего ключа, которое наиболее часто используется в реальных базах данных, а затем разберем возможные опции.
Декларативная форма ограничения внешнего ключа чаще всего используется, когда задается набор полей (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_TABLE подразумевается используемым в качестве внешнего ключа. Более полная форма создания внешнего ключа одновременно с таблицей приведена в следующем примере:
CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL, CONSTRAINT fkinv FOREIGN KEY (FK_TABLE) REFERENCES TITLE(ID_TITLE) …);
Использование NULL в полях внешнего ключа
В полях, на основе которых создается внешний ключ, допускается применение NULL-полей. Эта возможность добавлена для обеспечения взаимных ссылок. Например, если есть две таблицы, ссылающиеся друг на друга с помощью внешних ключей. Если не допускать пустую ссылку (т.е. NULL) в этих внешних ключах, будет невозможно добавить ни одну запись в связанные таблицы: чтобы добавить запись в первую таблицу, требуется наличие записи во второй таблице, и наоборот.
Использование NULL в качестве пустой ссылки позволяет создавать взаимные ссылки двух перекрестно ссылающихся таблиц, а также хранить иерархические структуры в реляционных таблицах - при этом корневые узлы ссылаются на «пустые» записи (т.е. просто содержат NULL).
Расширенные возможности поддержки ссылочной целостности с использованием внешних ключей
Обычно декларативного варианта ограничения внешнего ключа вполне достаточно: сервер лишь следит за тем, чтобы в таблицу с внешним ключом было невозможно вставить некорректные значения, или - при попытке сделать это - появляется ошибка. Но InterBase позволяет выполнять набор автоматических операций при изменении/удалении внешнего ключа. Для этой цели используется следующий набор опций внешнего ключа:
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|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).
Таблица 1.2
| Событие | Операция | |||
| NO ACTION | CASCADE | SET DEFAULT | SET NULL | |
| ON DELETE | При удалении внешнего ключа ничего не делать - используется по умолчанию | При удалении удалить все связанные записи из подчиненной таблицы | При изменении установить поле внешнего ключа в значение по умолчанию |
При изменении установить поле внешнего ключа в NULL |
| ON UPDATE | При изменении ничего не делать - используется по умолчанию | При изменении записи изменить все связанные записи в подчиненной таблице | При удалении установить поле внешнего ключа в значение по умолчанию |
При удалении установить поле внешнего ключа в NULL |
Если мы ничего не указываем или указываем NO ACTION, нам следует самостоятельно позаботиться об изменении внешнего ключа (в случае изменения первичного), а при удалении первичного ключа заранее удалить записи из подчиненной таблицы. Будьте очень внимательны при использовании опции CASCADE: ее неосторожное использование может привести к удалению большого количества связанных записей.
Ограничение CHECK
Одним из наиболее полезных ограничений в базе данных является ограничение check. Его функция очень проста - проверить значение, вставляемое в таблицу, на соответствие любому условию, и в зависимости от выполнения этого условия вставить данные или нет. Его синтаксис довольно прост:
= [CONSTRAINT constraint] CHECK ( )}
Здесь constraint - имя ограничения; - поисковое условие, в котором вставляемое/обновляемое значение может быть параметром. Если поисковое условие выполняется, разрешается вставить/обновить это значение, если нет - появляется ошибка. Простейший пример check:
create table checktst( ID integer CHECK(ID>0));
Этот check определяет, больше ли нуля вставляемое/обновляемое значение поля ID, и в зависимости от результата разрешает вставить/обновить новое значение или сообщает об ошибке (см. главу «Расширенные возможности языка хранимых процедур InterBase» (часть 1)).
Существуют и более сложные варианты проверок. Полный синтаксис поискового условия выглядит следующим образом:
= {
{ | ()}
| [NOT] BETWEEN AND
| [NOT] LIKE [ESCAPE ]
| [NOT] IN ( [ , …] | )
| IS [NOT] NULL
| {[NOT] {= | < | >} | >= | <=}
{ALL | SOME | ANY} ()
| EXISTS ( )
| SINGULAR ( )
| [NOT] CONTAINING
| [NOT] STARTING [WITH]
| ()
| NOT
| OR
| AND }
Таким образом, CHECK предоставляет большой набор возможностей для проверки вставляемых/обновляемых значений. При использовании CHECK следует помнить о следующих ограничениях:
- Данные для CHECK берутся только из текущей записи. Не следует брать данные для выражения в CHECK из других записей той же таблицы - они могут быть изменены другими пользователями
- Поле может иметь только одно ограничение CHECK
- Если для определения поля используется домен, имеющий ограничение CHECK, его нельзя переопределить на уровне конкретного поля в таблице. Следует отметить, что CHECK реализуются системными триггерами, поэтому нужно быть осторожнее при использовании очень длинных условий, которые могут сильно замедлить процессы вставки и обновления записей.
Удаление ограничений
Очень часто мы удаляем различные ограничения по самым разным причинам. Для удаления ограничения следует использовать оператор ALTER TABLE следующего вида: ALTER TABLE tablename DROP CONSTRAINT constraintname
constraintname - это имя ограничения, которое должно быть удалено. Если при создании ограничения было указано определенное имя, следует использовать его, если же нет, нам придется открыть любой инструмент администрирования InterBase, найти все ограничения, связанные с ним, и выяснить, какое системное имя InterBase сгенерировал для требуемого ограничения.
Следует отметить, что удалять ограничения может только владелец таблицы или системный администратор SYSDBA.