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

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

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

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

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

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

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

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

Но в 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;

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

Механизмы управления ссылками внешних ключей

Внешний ключ имеет такой синтаксис:

FOREIGN KEY (список_столбцов_дочерней_таблицы)
REFERENCES <имя_родительской_таблицы>
[<список_столбцов_родительской_таблицы>]
[ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
[ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]

Разберем этот синтаксис.

список_столбцов_дочерней_таблицы - это один или несколько столбцов, которые являются внешним ключом.

<имя_родительской_таблицы> - имя родительской таблицы, на которую ссылается внешний ключ дочерней таблицы.

[<список_столбцов_родительской_таблицы>] - один или несколько столбцов, являющихся ключевыми для связи таблиц. Это необязательный параметр, его можно не указывать, если связь строится по первичному ключу родительской таблицы, но обязательный, если связываемся с ключом UNIQUE.

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

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

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

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

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

В приведенном выше примере с родительской и дочерней таблицами мы указали, что при изменении значения первичного ключа родительской таблицы, следует изменить это же значение во всех записях внешнего ключа дочерней таблицы (см. рис.18.3). А при удалении значения первичного ключа ничего делать с дочерней таблицей не нужно. Вообще то, с параметром ON DELETE CASCADE следует быть очень осторожным: случайная ошибка пользователя может привести к потере большого количества связанных данных, имейте это в виду. Также следует быть внимательными при использовании атрибута NO ACTION. При удалении или изменении записи в родительской таблице, связанные с ней записи в дочерней таблице не изменятся. А это означает, что база данных станет недостоверной.

Именование ссылочной целостности

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

CONSTRAINT <Имя_ссылочной_целостности>

Создадим еще две таблицы, одна из которых ссылается на другую:

CREATE TABLE Roditel2(
   R_ID VARCHAR(20) NOT NULL PRIMARY KEY,
   R_Celoe INT);
COMMIT;

CREATE TABLE Doch2(
   D_ID VARCHAR(20),
   D_Celoe INT,
   CONSTRAINT Cons_Doch2  FOREIGN KEY (D_ID) REFERENCES Roditel2 
   ON UPDATE CASCADE ON DELETE NO ACTION);
COMMIT;

Чтобы удалить эти таблицы, нужно вначале удалить ссылочную целостность:

ALTER TABLE Doch2
DROP CONSTRAINT Cons_Doch2

Внимание! При удалении ограничения вы можете получить ошибку "object is in use" (объект находится в использовании). Это говорит о том, что на какую-то из таблиц имеется незавершенная транзакция.

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

DROP TABLE Doch2;
DROP TABLE Roditel2;

Еще одно важное замечание: в InterBase нет ссылочных целостностей без идентификатора! Если вы не дали имени ссылочной целостности, InterBase делает это автоматически. Выделите в IBConsole пункт Tables, чтобы в правой части окна появился список таблиц базы данных. Затем щелкните правой кнопкой по таблице DOCH из первого примера (именно в ней мы создавали внешний ключ без имени), и выберите команду Properties. Откроется знакомое вам окно, в котором нужно щелкнуть по кнопке Show Check Constraints:

В окне вы увидите имя ограничения, которое автоматически было дано InterBase, у меня это INTEG _31, у вас оно может быть другим. Теперь, зная имя ограничения, самостоятельно удалите его, после чего удалите таблицы DOCH и RODITEL.

Индексы

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

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

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

CREATE [UNIQUE] {[ASC[ENDING] | DESC[ENDING]]}
INDEX <IndexName> ON <TableName> (<col> [, <col> … ]);

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

CREATE INDEX Sklad_Index ON SKLAD(ID_TOVAR)

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

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

Необязательный параметр ASC или ASCENDING указывает, что индекс должен сортироваться в возрастающем порядке, а DESC ( DESCENDING ) - в убывающем.

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

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

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

DROP INDEX <Index_Name>

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

ALTER INDEX <Index_Name> INACTIVE;
ALTER INDEX <Index_Name> ACTIVE;

Первая команда отключает индекс, вторая подключает его вновь. Имеется ряд ограничений на эти действия:

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