Отношение многие-ко-многим

Отношение многие-ко-многим

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