Хелен Борри - Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ

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 важно обеспечить такие действия ограничений, которые бы не создавали случайно зависшие дочерние строки.

CREATE TABLE PARENT_CHILD (

ID INTEGER NOT NULL,

PARENT_ID INTEGER

CHECK (PARENT_ID <> ID));

COMMIT;

ALTER TABLE PARENT_CHILD

ADD CONSTRAINT PK_PARENT

PRIMARY KEY(ID);

COMMIT;

ALTER TABLE PARENT_CHILD

ADD CONSTRAINT FK_CHILD_PARENT

FOREIGN KEY(PARENT_ID)

REFERENCES PARENT_CHILD(ID);

Можно было бы сказать гораздо больше о проектировании древовидных структур. Это перспективная тема в создании реляционных баз данных, которая расширяет

границы стандарта SQL. К сожалению, она выходит за рамки данной книги. Некоторые интересные решения см. в "SQL for Smarties", 2nd Edition by Joe Celko (Morgan Kaufmann, 1999).