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

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).

Обязательные отношения

Обязательное отношение - это отношение, которое требует существования как минимум одной дочерней строки для каждой родительской строки. Например, структура накладной (заголовок с информацией о покупателе и адресом поставки) будет нелогичной, если будет разрешено существование заголовочной строки без детальных строк.

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

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

Подробности написания триггеров, а также пример триггера для поддержания обязательного отношения см. в главе 31.

Ошибка "объект находится в использовании"

Исключение "object is in use" (объект находится в использовании) заслуживает внимания в контексте применения ограничений ссылочной целостности, поскольку является постоянным источником огорчений для новичков. Firebird не позволяет добавлять или удалять ссылочное ограничение FOREIGN KEY, если транзакция использует любую из участвующих таблиц.

Иногда для вас может быть не столь очевидным, каким образом объект находится в использовании. Другие зависимости - такие как хранимые процедуры или триггеры, которые ссылаются на ваши таблицы, или другие ссылочные ограничения, воздействующие на одну или обе таблицы - могут вызвать это исключение, если таблицы используются в неподтвержденной транзакции. Кэш метаданных (блоки памяти на сервере, которые содержат метаданные, сформированные в результате выполнения последних клиентских запросов, и коды вызванных хранимых процедур и триггеров) хранит блокировки применяемых объектов. Каждое соединение имеет свой собственный кэш метаданных, даже в случае Суперсервера, следовательно, сервер может хранить блокировки объектов, которые фактически не используют ни одно соединение.

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