Читать интересную книгу Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ - Хелен Борри

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

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

Заманчивым является применение формального ограничения первичного ключа к столбцам, которые ссылаются на таблицы соответствия. Аргументом для этого является то, что каскадное ссылочное ограничение будет обеспечивать согласованность данных. Изъян здесь в том, что правильно спроектированные таблицы соответствия никогда не изменяют их ключи, следовательно, здесь не может появиться противоречивости, от которой требуется защита.

Посмотрите на следующий пример отношения соответствия, являющийся наследием конвертирования очень плохо спроектированного приложения для Access в Firebird. Клиентские приложения Access могут выполнять изящные действия с целыми таблицами, которые позволяют дилетантам создавать приложения RAD. Эта таблица была использована в визуальном управляющем элементе, который мог отображать таблицу и "перемещать" значение в другую таблицу при щелчке по кнопке.

CREATE TABLE COLORS (COLOR CHARACTER(20) NOT NULL PRIMARY KEY);

Фрагмент DDL одной из таблиц, которая использует COLORS В качестве таблицы соответствия:

CREATE TABLE STOCK_ITEM (

. . .

COLOR CHARACTER(20) DEFAULT 'NEUTRAL',

. . .

CONSTRAINT FK_COLOR FOREIGN KEY (COLOR)

REFERENCES COLORS(COLOR)

ON UPDATE CASCADE

ON DELETE SET DEFAULT;

Существует множество проблем с этим ключом. Во-первых, таблица COLORS была доступна покупателям товаров для ее редактирования, как они считали нужным. Изменения выполнялись каскадно по всей системе всякий раз, когда новые элементы добавлялись в ассортимент. Удаления часто убирают информацию о цвете в относительно небольшом количестве элементов, в которых она используется. Хуже того, основная масса элементов в системе имела один цвет 'NEUTRAL', в результате чего индекс внешнего ключа ухудшал выполнение запросов.

"Реляционный путь" - устранение незапланированных нарушений данных за счет использования ключа соответствия, который будет содержать не имеющие смысл данные (т. е. атомарный ключ):

CREATE TABLE COLORS (

ID INTEGER NOT NULL PRIMARY KEY, /* or UNIQUE */

COLOR CHARACTER(20));

COMMIT;

INSERT INTO COLORS (ID, COLOR)

VALUES (0, 'NEUTRAL');

COMMIT;

CREATE TABLE STOCK_ITEM (

. . .

COLOR INTEGER DEFAULT 0,

. . .);

Такой ключ никогда не нужно изменять; он может (и должен) быть спрятан от пользователей. Таблицы, использующие таблицы соответствия, хранят стабильный ключ. Все допустимые изменения реализуются как новые строки таблицы соответствия с новыми ключами. Значения, уже связанные с ключами, не изменяются- они защищены от того, чтобы история данных подвергалась риску последующих изменений.

В этом случае, даже при большом распределении значений ключа внешний ключ будет создавать индекс, который все еще будет иметь плохую селективность в большой таблице; повышение стабильности таблицы оправдывает отказ от формального ссылочного ограничения. Существование строки с соответствующим первичным ключом легко можно проверить с помощью пользовательского триггера.

Привилегии на ссылки

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

Влияние на производительность "разреженного" ключа (такого, какой использован в этом примере) не может быть сильным. В случае составного ключа эффект может быть значительным, особенно в случае множественных соединений, включающих отношения один-к-одному. Следует рассмотреть использование суррогатного ключа в структурах один-к-одному[51].

! ! !

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

. ! .

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

В этом интересном случае, показанном на рис. 17.2, наша модель данных показывает, что каждая строка в таблице TableA может иметь отношения со множеством строк таблицы TableB, и в то же время каждая строка в TableB может иметь множественные отношения со строками В TableA.

Рис. 17.2. Отношения многие-ко-многим

Это отношение использует условие, называемое циклической ссылкой. Предлагаемый внешний ключ в таблице TableB ссылается на первичный ключ таблицы TableA, что означает, что строка таблицы TableB не может быть создана, если в таблице TableA нет строки с соответствующим первичным ключом. В то же время, по этой же причине требуемая строка не может быть добавлена в таблицу TableA, если не существует соответствующего значения первичного ключа в таблице TableB.