Отношение многие-ко-многим
Отношение многие-ко-многим
В этом интересном случае, показанном на рис. 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;