Опубликован: 07.05.2010 | Уровень: специалист | Доступ: платный
Лекция 18:

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

< Лекция 17 || Лекция 18: 12 || Лекция 19 >

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

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

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:

Кнопка 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 ) или быть создателем данного индекса.

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

< Лекция 17 || Лекция 18: 12 || Лекция 19 >
Евгений Медведев
Евгений Медведев

В лекции №2 вставляю модуль данных. При попытке заменить name на  fDM выдает ошибку: "The project already contains a form or module named fDM!". Что делать? 

Анна Зеленина
Анна Зеленина

При вводе типов успешно сохраняется только 1я строчка. При попытке ввести второй тип вылезает сообщение об ошибке "project mymenu.exe raised exception class EOleException with message 'Microsoft Драйвер ODBC Paradox В операции должен использоваться обновляемый запрос'. 

Денис Попов
Денис Попов
Россия, Оренбург, Оренбургский государственный университет, 2015
Рустам Кадыров
Рустам Кадыров
Россия, Тирлян, Тирлянская школа №5, 2003