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

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

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

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:

Рис. 18.4. Кнопка 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;

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

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

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