Ключи и индексы

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

Базы данных InterBase могут использовать следующие виды ограничений:

• PRIMARY KEY - первичный ключ таблицы.
• UNIQUE - уникальный ключ таблицы.
• FOREIGN KEY - внешний ключ, обеспечивает ссылку на другую таблицу и гарантирует ссылочную целостность между родительской и дочерней таблицами.

Примечание о терминологии

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

Связано это, вероятно, с тем, как интерпретируются эти определения в локальных и SQL -серверных СУБД.

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

Рис. 18.1. Связи главная-подчиненная

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

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

Так что в приведенном выше примере таблица продаж имеет два внешних ключа: идентификатор товара, и идентификатор покупателя. А обе таблицы в правой части рисунка имеют родительский ключ "Идентификатор". Поскольку один покупатель или товар могут неоднократно встречаться в таблице продаж, то получается, что обе таблицы в правой части рисунка - родители, а таблица слева - дочерняя. Поскольку сейчас мы изучаем InterBase - SQL сервер БД, этими определениями мы и будем руководствоваться в последующих лекциях. Чтобы далее не ломать голову над этой путаницей, сразу договоримся: дочерняя таблица имеет внешний ключ ( FOREIGN KEY ) на другую таблицу.

PRIMARY KEY

PRIMARY KEY - первичный ключ, является одним из основных видов ограничений в базе данных. Первичный ключ предназначен для однозначной идентификации записи в таблице, и должен быть уникальным. Первичные ключи PRIMARY KEY находятся в таблицах, которые принято называть родительскими ( Parent ). Не стоит путать первичный ключ с первичными индексами локальных баз данных, первичный ключ является не индексом, а именно ограничением. При создании первичного ключа InterBase автоматически создает для него уникальный индекс. Однако если мы создадим уникальный индекс, это не приведет к созданию ограничения первичного ключа. Таблица может иметь только один первичный ключ PRIMARY KEY.

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

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

Если в первичный ключ входит единственный столбец (как чаще всего и бывает), спецификатор PRIMARY KEY ставится при определении столбца:

CREATE TABLE Prim_1(
  Stolbec1 INT NOT NULL PRIMARY KEY,
  Stolbec2 VARCHAR(50))

Если первичный ключ строится по нескольким столбцам, то спецификатор ставится после определения всех полей:

CREATE TABLE Prim_2(
  Stolbec1 INT NOT NULL,
  Stolbec2 VARCHAR(50) NOT NULL,
 PRIMARY KEY (Stolbec1, Stolbec2))

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

UNIQUE

UNIQUE - уникальный ключ. Спецификатор UNIQUE указывает, что все значения данного поля должны быть уникальными, в связи с этим такие поля также не могут содержать значения NULL. Можно сказать, что уникальный ключ UNIQUE является альтернативным вариантом первичного ключа, однако имеются различия. Главное различие в том, что первичный ключ должен быть только один, тогда как уникальных ключей может быть несколько. Кроме того, ограничение UNIQUE не может быть построено по тому же набору столбцов, который был использован для ограничения PRIMARY KEY или другого UNIQUE. Уникальные ключи, как и первичные, находятся в таблицах, которые являются родительскими по отношению к другим таблицам.

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

CREATE TABLE Prim_3(
  Stolbec1 INT NOT NULL PRIMARY KEY,
  Stolbec2 VARCHAR(50) NOT NULL UNIQUE,
  Stolbec3 FLOAT NOT NULL UNIQUE)

FOREIGN KEY

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

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

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

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

В отличие от первичного ключа, ключ FOREIGN KEY может содержать пустое значение, для него не обязателен атрибут NOT NULL. Строки с пустым внешним ключом не ссылаются ни на какую запись родительской таблицы, и называются "зависшими". Чтобы продемонстрировать работу с внешним ключом, создадим две таблицы - родительскую и дочернюю:

CREATE TABLE Roditel(
   R_ID VARCHAR(20) NOT NULL PRIMARY KEY,
   R_Other INT);
COMMIT;

CREATE TABLE Doch(
   D_ID VARCHAR(20),
   D_Other INT,
   FOREIGN KEY (D_ID) REFERENCES Roditel 
   ON UPDATE CASCADE ON DELETE NO ACTION);
COMMIT;

Рис. 18.2. Результат совместной работы родительской и дочерней таблиц

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

1. Вначале вводится значение первичного ключа ( R_ID ) в родительскую таблицу.
2. Затем вводится такое же значение во внешний ключ ( D_ID ) дочерней таблицы. Попытка ввести значение, которого нет в поле R_ID родительской таблицы, потерпит неудачу. Зато можно не вводить это значение, оставив в поле NULL, или ввести несколько записей с одинаковым значением.
3. Изменение текста в поле R_ID родительской таблицы приведет к автоматическому изменению такого же текста во всех записях дочерней таблицы, где этот текст встречается (об этом чуть ниже):
   

   
   Рис. 18.3. Каскадные изменения записей внешнего ключа дочерней таблицы.
4. Попытка удалить дочернюю таблицу командой DROP приведет к ошибке: она ссылается на родительскую таблицу.
5. Попытка удалить родительскую таблицу приведет к ошибке: для сохранения целостности данных InterBase не даст удалить первичный ключ.
6. Для удаления этих таблиц вначале придется удалить ограничения, наложенные на них (об этом чуть ниже).