Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ

! ! !

СОВЕТ. Если вам нужно сделать столбец вашего внешнего ключа NOT NULL, создайте "фиктивную" строку родительской таблицы с неиспользуемым значением ключа, например, 0 или -1. Используйте действие SET DEFAULT для эмуляции поведения SET NULL, чтобы сделать значением по умолчанию фиктивное значение ключа.

. ! .

Ссылочные ограничения могут быть назначены ограничениям CHECK. В некоторых случаях ограничение CHECK, наследуемое от домена, может также пересекаться или вступать в конфликт со ссылочным ограничением. Стоит потратить несколько минут для описания на бумаге эффектов каждого ограничения для идентификации и уменьшения потенциальных проблем.

Триггеры действий пользователя

Есть прекрасная возможность написания собственных триггеров для выполнения дополнительных действий по поддержке ссылочной целостности. Хотя автоматические триггеры достаточно гибкие для того, чтобы предусмотреть большинство требований, существует один особый случай, для которого обычно вызываются пользовательские триггеры. Это тот случай, когда создание обязательного индекса, поддерживающего столбец внешнего ключа, нежелательно, потому что это будет индекс с очень низкой селективностью.

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

! ! !

СОВЕТ. Селективность индекса довольно подробно обсуждается в главе 18. Если эта тема для вас новая, то это может вас заставить основательно разобраться в данном вопросе до принятия решения реализовывать отношение один- ко-многим в вашей модели данных с использованием формальных ограничений целостности "только потому, что я могу".

. ! .

При написании пользовательских ссылочных триггеров вы должны убедиться, что ваши собственные триггеры или ваше приложение будут поддерживать ссылочную целостность при изменении данных в любом ключе. Триггеры более безопасны, чем код приложений, поскольку они централизуют правила целостности данных в базе данных и поддерживают их для всех типов доступа к данным, будь то программы, утилиты, скрипты или приложение серверного уровня [50] .

50

Не следует полагаться на триггерную ссылочную целостность потому, что ограничения PRIMARY KEY, FOREIGN KEY и UNIQUE работают вне контекста транзакций (т. е. "видят" все версии записей), а пользовательские триггеры - в контексте пользовательских транзакций. В результате пользовательский триггер, проверяющий наличие определенной записи, никоим образом не узнает, что эта запись на самом деле уже удалена или изменена в другой, конкурирующей, транзакции.
– Прим. науч. ред.

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

Таблицы соответствия и ваша модель данных

Мы часто используем таблицы соответствия (lookup tables) - также называемые управляющими таблицами (control tables) или таблицами определения (definition tables) - для хранения статичных строк, которые могут содержать расширенные тексты, коэффициенты преобразования, а также нечто подобное выходным наборам, часто получаемым в приложениях как списки выбора. Примерами являются таблицы "типов", которые содержат сущности, такие как типы счетов или типы документов, таблицы "коэффициентов", используемые для преобразования валют или вычисления налогов, и таблицы "соответствия кодов", хранящие такие элементы, как коды, соответствующие цвету. Динамичные таблицы связаны с такими статичными таблицами через соответствие ключа первичному ключу статичных таблиц.

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

В реализованных базах данных такое отношение не является отношением главная- подчиненная, или родитель-потомок, потому что значение первичного ключа у набора соответствия требует одного и только одного столбца. Это не влияет на другие отношения, в которых участвует этот "псевдопотомок".

Заманчивым является применение формального ограничения первичного ключа к столбцам, которые ссылаются на таблицы соответствия. Аргументом для этого является то, что каскадное ссылочное ограничение будет обеспечивать согласованность данных. Изъян здесь в том, что правильно спроектированные таблицы соответствия никогда не изменяют их ключи, следовательно, здесь не может появиться противоречивости, от которой требуется защита.

Посмотрите на следующий пример отношения соответствия, являющийся наследием конвертирования очень плохо спроектированного приложения для Access в Firebird. Клиентские приложения Access могут выполнять изящные действия с целыми таблицами, которые позволяют дилетантам создавать приложения RAD. Эта таблица была использована в визуальном управляющем элементе, который мог отображать таблицу и "перемещать" значение в другую таблицу при щелчке по кнопке.

CREATE TABLE COLORS (COLOR CHARACTER(20) NOT NULL PRIMARY KEY);

Фрагмент DDL одной из таблиц, которая использует COLORS В качестве таблицы соответствия:

CREATE TABLE STOCK_ITEM (

. . .

COLOR CHARACTER(20) DEFAULT 'NEUTRAL',

. . .

CONSTRAINT FK_COLOR FOREIGN KEY (COLOR)

REFERENCES COLORS(COLOR)

ON UPDATE CASCADE

ON DELETE SET DEFAULT;

Существует множество проблем с этим ключом. Во-первых, таблица COLORS была доступна покупателям товаров для ее редактирования, как они считали нужным. Изменения выполнялись каскадно по всей системе всякий раз, когда новые элементы добавлялись в ассортимент. Удаления часто убирают информацию о цвете в относительно небольшом количестве элементов, в которых она используется. Хуже того, основная масса элементов в системе имела один цвет 'NEUTRAL', в результате чего индекс внешнего ключа ухудшал выполнение запросов.

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

CREATE TABLE COLORS (

ID INTEGER NOT NULL PRIMARY KEY, /* or UNIQUE */

COLOR CHARACTER(20));

COMMIT;

INSERT INTO COLORS (ID, COLOR)

VALUES (0, 'NEUTRAL');

COMMIT;

CREATE TABLE STOCK_ITEM (

. . .

COLOR INTEGER DEFAULT 0,

. . .);

Такой ключ никогда не нужно изменять; он может (и должен) быть спрятан от пользователей. Таблицы, использующие таблицы соответствия, хранят стабильный ключ. Все допустимые изменения реализуются как новые строки таблицы соответствия с новыми ключами. Значения, уже связанные с ключами, не изменяются- они защищены от того, чтобы история данных подвергалась риску последующих изменений.

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