Привилегии на ссылки
Firebird поддерживает безопасность SQL для всех объектов в базе данных. Каждый пользователь, за исключением владельца базы данных, пользователя SYSDBA или с системными привилегиями root, должен получить (при использовании GRANT) необходимые привилегии доступа к объекту. Привилегии SQL очень подробно обсуждаются в главе 3 7.
Тем не менее одна привилегия очень важна при проектировании инфраструктуры ссылочной целостности - привилегия REFERENCES. ЕСЛИ родительская и дочерняя таблицы имеют разных владельцев, привилегия GRANT REFERENCES может оказаться необходимой для предоставления пользователям достаточных полномочий для действий ссылочного ограничения.
Привилегия REFERENCES предоставляется для таблицы, на которую осуществляется ссылка в отношении, - т. е. для таблицы, на которую ссылается внешний ключ, - или, по крайней мере, на каждый столбец первичного или уникального ключа. Привилегия должна быть предоставлена для владельца ссылающейся таблицы (дочерней таблицы), а также для любого пользователя, которому необходимы права записи на ссылающуюся таблицу.
Во время выполнения REFERENCES срабатывает, когда сервер базы данных устанавливает, что вводимое во внешний ключ значение находится в таблице, на которую осуществляется ссылка.
Поскольку такая привилегия проверяется при определении ограничения внешнего ключа, необходимо предоставить и подтвердить соответствующие разрешения заблаговременно. Если вам нужно создать внешний ключ, который ссылается на таблицу, которой владеет кто-то другой, то владелец должен предоставить вам привилегии REFERENCES к этой таблице. Альтернативно владелец может предоставить привилегии REFERENCES роли, а затем предоставить вам эту роль.
! ! !
СОВЕТ. Не делайте это сложнее, чем оно должно быть. Если нет никакого требования отменять привилегии чтения для таблицы, на которую осуществляются ссылки, то передайте привилегию REFERENCES К ней для всех (PUBLIC).
. ! .
Если ваши требования содержат такие ограничения, вам может понадобиться поддерживать два разрешающих скрипта: один для разработчиков, выполняющих создание таблицы, и другой для пользователей, работающих с созданной схемой.
Обработка других видов отношений
Ограничения целостности могут быть применены для других форм отношений, помимо формы один-ко-многим, описанной до настоящего времени.
* Один-к-одному.
* Многие-ко-многим.
* Ссылающееся на себя отношение один-ко-многим (вложенные или древовидные отношения).
* Обязательные варианты любых форм отношений.
Отношение один-к-одному
Структуры один-к-одному могут быть полезными, когда сущность в вашей модели данных имеет множество различных атрибутов, из которых только к некоторым часто осуществляется доступ. Это может резко сократить занимаемую память и время чтения страниц, если хранить случайные данные в необязательных "подчиненных" отношениях, которые используют соответствующие первичные ключи.
Отношение один-к-одному похоже на отношение один-ко-многим в том смысле, что оно связывает внешний ключ с уникальным ключом. Разница здесь в том, что связываемый ключ должен быть уникальным для поддержания отношения один-к-одному - чтобы соединить не более одной зависимой строки с одной родительской строкой.
Обычным является дублирование столбца (столбцов) первичного ключа в подчиненной таблице в качестве внешнего ключа для "родительской".
CREATE TABLE PARENT_PEER (
ID INTEGER NOT NULL,
MORE_DATA VARCHAR(10),
CONSTRAINT PK_PARENT_PEER PRIMARY KEY(ID),
CONSTRAINT FK_PARENT_PEER_PARENT
FOREIGN KEY (ID) REFERENCES PARENT);
Результатом такого дублирования является создание двух обязательных индексов для столбца первичного ключа подчиненной таблицы: один для первичного ключа и один для внешнего ключа. Индекс внешнего ключа сохраняется так, как если бы он не был уникальным.
В версиях 1.0.x и 1.5 оптимизатор игнорирует первичный индекс подчиненной таблицы. Например:
SELECT PARENT.ID, PARENT_PEER.ID,
PARENT.DATA, PARENT_PEER.MORE_DATA
FROM PARENT JOIN PARENT_PEER
ON PARENT.ID = PARENT_PEER.ID;
игнорирует индекс первичного ключа подчиненной таблицы и создает план:
PLAN JOIN (PARENT_PEER NATURAL, PARENT INDEX (PK_PARENT) )
Влияние на производительность "разреженного" ключа (такого, какой использован в этом примере) не может быть сильным. В случае составного ключа эффект может быть значительным, особенно в случае множественных соединений, включающих отношения один-к-одному. Следует рассмотреть использование суррогатного ключа в структурах один-к-одному.
! ! !
СОВЕТ. Не будет плохо, если вы решите добавить специальный столбец для подчиненного отношения с целью разделения первичного и внешнего ключей. Это может оказаться полезным и для документирования.
. ! .
Отношение многие-ко-многим
В этом интересном случае, показанном на рис. 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 иллюстрирует, как таблица пересечения реализует отношение многие-ко-многим.