Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ
Шрифт:
! ! !
СОВЕТ. Не будет плохо, если вы решите добавить специальный столбец для подчиненного отношения с целью разделения первичного и внешнего ключей. Это может оказаться полезным и для документирования.
. ! .
Отношение многие-ко-многим
В этом интересном случае, показанном на рис. 17.2, наша модель данных показывает, что каждая строка в таблице TableA может иметь отношения со множеством строк таблицы TableB, и в то же время каждая строка в TableB может иметь множественные отношения со строками В TableA.
Рис. 17.2.
Это отношение использует условие, называемое циклической ссылкой. Предлагаемый внешний ключ в таблице TableB ссылается на первичный ключ таблицы TableA, что означает, что строка таблицы TableB не может быть создана, если в таблице TableA нет строки с соответствующим первичным ключом. В то же время, по этой же причине требуемая строка не может быть добавлена в таблицу TableA, если не существует соответствующего значения первичного ключа в таблице TableB.
Если ваши структурные требования диктуют необходимость существования подобных циклических ссылок, это можно сделать обходным путем. Firebird позволяет внешнему ключу иметь значение NULL - если не указывать для столбца ограничение NOT NULL, - поскольку NULL означает отсутствие значения. Это не нарушит правила, по которому столбец внешнего ключа должен иметь соответствие в столбце родительской таблицы, на которую ссылается внешний ключ. Присваивая значение NULL внешнему ключу одной таблицы, вы можете добавлять строку в эту таблицу, создавая первичный ключ, требуемый в другой таблице:
CREATE TABLE TABLEA (
ID INTEGER NOT NULL,
. . .,
CONSTRAINT PK_TABLEA PRIMARY KEY (ID));
COMMIT;
CREATE TABLE TABLEB (
ID INTEGER NOT NULL,
. . . ,
CONSTRAINT PK_TABLEB PRIMARY KEY (ID));
COMMIT;
ALTER TABLE TABLEA
ADD CONSTRAINT FK_TABLEA_TABLEB
FOREIGN KEY(IDB) REFERENCES TABLEB(ID);
COMMIT;
ALTER TABLE TABLEB
ADD CONSTRAINT FK_TABLEB_TABLEA
FOREIGN KEY(IDA) REFERENCES TABLEA(ID);
COMMIT;
Вот этот прием:
INSERT INTO TABLEB(ID)
VALUES(1);
/* создает строку со значением NULL в столбце IDB */
COMMIT;
INSERT INTO TABLEA(ID, IDB)
VALUES(22, 1);
/* связывает с только что созданной строкой в TABLEB */
COMMIT;
UPDATE TABLEB
SET IDA = 22 WHERE ID = 1;
COMMIT;
Понятно, что эта модель не лишена потенциальных проблем. В большинстве систем ключи генерируются, а не поставляются приложениями. Чтобы обеспечить согласованность, описанная работа выполняется для всех клиентских приложений, добавляющих данные в эти таблицы, чтобы они обеспечивали значения обоих ключей для обеих таблиц в контексте одной транзакции. Выполнение единой операции в хранимой процедуре уменьшит зависимость кода приложения от такого отношения.
! ! !
ВНИМАНИЕ! На практике таблицы с отношением многие-ко-многим, реализованным циклически, очень сложно представить в приложениях с графическим интерфейсом.
. ! .
В большинстве случаев лучшей практикой разрешения отношения многие-ко-многим является добавление таблицы пересечения. Такая специальная структура имеет один внешний ключ для каждой таблицы в отношении многие-ко-многим. Ее собственный первичный ключ (или ограничение UNIQUE) состоит из двух внешних ключей. Две связанные этим отношением таблицы вовсе не имеют внешних ключей, связывающих одну с другой.
Такая реализация проста для использования в приложениях. Триггеры BEFORE INSERT (до добавления) и BEFORE UPDATE (до изменения) для обеих таблиц выполняют при необходимости добавление строки в таблицу пересечения. Рис. 17.3 иллюстрирует, как таблица пересечения реализует отношение многие-ко-многим.
Рис. 17.3. Реализация отношения многие-ко-многим
Вот как это может быть реализовано:
CREATE TABLE TABLEA (
ID INTEGER NOT NULL,
. . . ,
CONSTRAINT PK_TABLEA PRIMARY KEY (ID));
COMMIT;
CREATE TABLE TABLEB (
ID INTEGER NOT NULL,
CONSTRAINT PK_TABLEB PRIMARY KEY (ID));
COMMIT;
/**/
CREATE TABLE TABLEA_TABLEB (
IDA INTEGER NOT NULL,
IDB INTEGER NOT NULL,
CONSTRAINT PK_TABLEA_TABLEB
PRIMARY KEY (IDA, IDB));
COMMIT;
ALTER TABLE TABLEA_TABLEB
ADD CONSTRAINT FK_TABLEA FOREIGN KEY (IDA)
REFERENCES TABLEA,
ADD CONSTRAINT FK_TABLEB FOREIGN KEY (IDB)
REFERENCES TABLEB;
COMMIT;
Ссылающиеся на себя отношения
Если ваша модель имеет сущность, у которой первичный ключ ссылается на внешний ключ, находящийся в той же сущности, то вы имеете ссылающееся на себя отношение, как показано на рис. 17.4.
Рис. 17.4. Ссылающееся на себя отношение
Это классическая древовидная иерархия, где любой элемент (строка) может быть и родителем, и потомком - т. е. строка может иметь зависящие от нее "дочерние" строки и в то же время она может зависеть от другого элемента (строки). Здесь требуется ограничение CHECK или триггеры BEFORE INSERT (до добавления) и BEFORE UPDATE (до изменения) для проверки того, чтобы PARENT_ID никогда бы не указывал сам на себя.
Если ваши бизнес-правила требуют, чтобы родитель существовал до того, как будет добавляться потомок, вам понадобится использование значения (например, -I) в качестве корневого узла в этой древовидной структуре. Тогда PARENT ID должен быть создан с NOT NULL и значением по умолчанию, равным выбранному вами значению корневого узла. Альтернативой является разрешение для PARENT ID пустого значения, как в следующем примере, и использование NULL в качестве значения корня.
Вообще пользовательские триггеры BEFORE INSERT (до добавления) и BEFORE UPDATE (до изменения) потребуются для деревьев, имеющих более двух уровней вложенности. Для согласованности деревьев с корневым узлом NULL важно обеспечить такие действия ограничений, которые бы не создавали случайно зависшие дочерние строки.