Внешний ключ и определение ссылочной целостности
Внешний ключ строится в дочерней таблице для соединения родительской и дочерних таблиц БД. Формат определения:
FOREIGN KEY (<список столбцов внешнего ключа>)
REFERENCES <имя родительской таблицы> [<список столбцов родительской таблицы>]
Список столбцов внешнего ключа
определяет столбцы дочерней таблицы, по которым строится внешний ключ.Имя родительской таблицы
определяет таблицу, в которой описан первичный ключ (или столбец с атрибутом UNIQUE). На этот ключ (столбец) должен ссылаться данный внешний ключ дочерней таблицы для обеспечения ссылочной целостности. Список столбцов родительской таблицы необязателен при ссылке на первичный ключ родительской таблицы. При ссылке в родительской таблице на столбец с атрибутом UNIQUE этот список лучше привести.Пример.
Определим две таблицы:• родительскую "Справочник товаров" SPR_TOVAR с полями TOVAR (наименование товара), ZENA_ED (цена за единицу измерения);
первичный ключ по полю TOVAR;
• дочернюю " Приход товара на склад" PRIHOD с полями ID_PRIHOD (номер прихода), DATAPRIH (дата прихода), TOVAR (товар), KOLVO
(количество прихода, ед.). Первичный ключ по полю ID_PRIHOD внешний ключ по полю TOVAR для обеспечения ссылочной целостности с таблицей SPR_TOVAR.
Тогда для определения данных таблиц в БД необходимо выполнить операторы
CREATE TABLE SPR_TOVAR(TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
ZENA_ED INTEGER NOT NULL, PRIMARY KEY(TOVAR)) ;
CREATE TABLE PRIHOD(ID_PRIHOD INTEGER NOT NULL PRIMARY KEY,
DATAPRIH DATE NOT NULL,TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
KOLVO INTEGER NOT NULL, FOREIGN KEY(TOVAR) REFERENCES SPR_TOVAR);
Теперь для таблицы SPR_TOVAR будет установлена блокировка удаления или изменения значения в столбце TOVAR записи, если в таблице PRIHOD имеются записи о приходе этого товара.
Заметим, что определение общих полей родительской и дочерней таблиц (полей связи) должно в точности совпадать. Если в родительской таблице объявить TOVAR VARCHAR(20) NOT NULL COLLATE PXW_CYRL,
а в дочерней объявить TOVAR VARCHAR(20) NOT NULL,
то из-за различий в порядке сортировки символов эти столбцы не будут фактически идентичными, из-за чего будут постоянно возникать ошибки нарушения ссылочной целостности.
ЗАМЕЧАНИЕ.
Если ссылочная целостность между таблицей Р (родительской) и С (дочерней) обеспечивается при помощи связки PRIMARY KEY - FOREIGN KEY, то InterBase запрещает:• изменять значение столбца связи в таблице Р;
• удалять запись в таблице Р,если для нее есть записи в таблице С с таким же значением поля связи.
Иными словами, связь таблиц по внешнему ключу блокирует каскадные изменения и удаления в таблицах Р и С. В том случае, если необходимо осуществлять каскадные воздействия на таблицу С при изменении (удалении) родительской записи в Р, целостность между таблицами поддерживают при помощи триггеров, а ограничение внешнего ключа удаляют. Сам внешний ключ в дочерней таблице может и не удаляться в том случае, если он используется в запросах (оператор SELECT) оптимизатором запросов InterBase. В этом случае он определяется как индекс (оператором CREATE INDEX). Об организации ссылочной целостности при помощи триггеров, а также оптимизации запросов к БД, см. ниже соответствующие разделы.
