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

! ! !

ВНИМАНИЕ! Не надо импортировать существующий "первичный индекс" из наследуемой системы, основанной на файлах, или создавать такой индекс в ожидании объявления ограничения первичного ключа. Firebird не может накладывать ограничение первичного ключа поверх существующего индекса - по крайней мере в существующих версиях, включая 1.5, - а оптимизатор запросов не будет правильно работать при дублировании индексов.

. ! .

Таблица может иметь только один первичный ключ. Когда вы определяете ограничение, Firebird автоматически создает требуемый индекс, используя множество именованных правил. Имена индексов первичных ключей обсуждаются далее.

! ! !

ВНИМАНИЕ! Если вы конвертируете базу данных в Firebird из любого другого источника за исключением InterBase или Oracle, то вы должны обратить особое внимание на схему в отношении имен и ограничений первичного ключа.

. ! .

Хотя само ограничение PRIMARY KEY не является ссылочным ограничением, оно обычно является обязательной частью любого ссылочного ограничения, будучи потенциальным объектом предложения REFERENCES ограничения FOREIGN KEY. Подробности см. в главе 17.

Выбор первичного ключа

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

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

* Атрибут NOT NULL должен быть объявлен для всех столбцов в группе из одного или более столбцов. Целостность ключа может быть осуществлена только сравнением значений, a NULL не является значением.

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

К этим теоретическим "установкам" должна быть добавлена третья.

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

Как реальные данные могут привести вас к неудаче

Используя таблицу EMPLOYEE базы данных employee.fdb из каталога /examples корневого каталога Firebird (employee.gdb в версии 1,0.x), давайте посмотрим, как реальные данные могут привести к ошибочности ваших теоретических предположений об уникальности. Вот объявление, которое показывает имеющие смысл данные, хранимые в этой таблице:

CREATE TABLE EMPLOYEE (

FIRST_NAME VARCHAR(15) NOT NULL,

/* предположение: служащий должен иметь имя */

LAST_NAME VARCHAR(20) NOT NULL,

/* предположение: служащий должен иметь фамилию */

PHONE_EXT VARCHAR(4),

HIRE_DATE DATE DEFAULT CURRENT_DATE NOT NULL,

DEPT_NO CHAR(3) NOT NULL,

JOB_CODE VARCHAR (5) NOT NULL,

JOB_GRADE SMALLINT NOT NULL,

JOB_COUNTRY VARCHAR(15) NOT NULL,

SALARY NUMERIC (15, 2) DEFAULT 0 NOT NULL,

FULL_NAME COMPUTED BY FIRST_NAME || ' ' || LAST_NAME ) ;

Фактически эта структура не имеет кандидата в ключи. Невозможно идентифицировать одну строку служащего, используя (FIRST_NAME, LAST_NAME) в качестве ключа, поскольку комбинация двух элементов с вероятностью от средней до высокой может дублироваться в организации. Мы не сможем сохранить записи двух служащих с именем John Smith.

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

Суррогатные ключи

Мы уже рассматривали суррогатный ключ во вводной теме о ключах в главе 14. Суррогатный первичный ключ - значение, гарантирующее уникальность и не имеющее смыслового содержания, которое используется в качестве заменителя ключа в структуре таблицы, которая не может предоставить кандидата на ключ в собственной структуре. По этой причине в таблицу EMPLOYEE добавляется EMP_NO (объявляется через домен) для выполнения роли суррогатного ключа:

CREATE DOMAIN EMPNO SMALLINT ;

COMMIT;

ALTER TABLE EMPLOYEE

ADD EMP_NO EMPNO NOT NULL,

ADD CONSTRAINT PK_EMPLOYEE

PRIMARY KEY(EMP_NO) ;

Эта база данных также содержит генератор с именем EMP_NO_GEN и триггер Before insert (перед добавлением) с именем SET_EMP_NO для таблицы EMPLOYEE для получения значения данного ключа в момент добавления новой строки. В разд. "Реализация автоинкрементных ключей" главы 31 эта техника описывается в деталях. Это рекомендованный способ реализации суррогатных ключей в Firebird.

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

Составные первичные ключи

В процессе анализа данных иногда в структуре данных можно отыскать единственный уникальный столбец. Теория советует найти два или более столбцов, сгруппированных вместе в качестве ключа, которые будут гарантировать уникальность строки. Когда множество столбцов объединяются для формирования ключа, такой ключ называется составным ключом (composite key) или иногда сложным ключом.

Если вы имеете опыт работы с такими СУБД, как Paradox, где использовали составные ключи для реализации иерархических отношений, вам, вероятно, будет тяжело расстаться с мыслью, что вам придется жить без них. Пока еще на практике составные ключи должны рассматриваться очень ограниченно в таких СУБД, как Firebird, где не выполняется проход по физическим индексным структурам на диске для реализации отношений.

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

* Составные ключи обычно являются составленными из неатомарных элементов ключа- т.е. выбранные столбцы имеют смысловое значение (они являются "значимыми данными") и, несомненно, уязвимы для внешних изменений и ошибок ручного ввода.

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

* Ключи - внешние, так же как и первичные - имеют постоянные индексы. Составные индексы имеют более строгие ограничения по размеру, чем индексы из одного столбца.

* Составные индексы имеют тенденцию к большим размерам. Большие индексы используют больше страниц базы данных, что приводит к тому, что индексные операции (сортировка, соединение и сравнение) выполняются медленнее, чем необходимо.

Атомарность столбцов первичного ключа

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

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

Синтаксис объявления первичного ключа

Можно использовать несколько вариантов синтаксиса для назначения ограничения PRIMARY KEY столбцу или группе столбцов. Все столбцы, являющиеся элементами первичного ключа, должны быть предварительно определены с атрибутом NOT NULL. Так как нельзя добавить ограничение NOT NULL в столбец после его создания, необходимо позаботиться об этом ограничении до использования других ограничений.

Ограничение PRIMARY KEY может применяться в любой из следующих фаз создания метаданных:

* в определении столбца в операторах CREATE TABLE или ALTER TABLE как часть определения столбца;

* в определении таблицы в операторах CREATE TABLE или ALTER TABLE как отдельно определенное ограничение таблицы.

Определение первичного ключа как часть определения столбца

В следующей последовательности создается и подтверждается (commit) домен, не допускающий пустое значение, затем определяется столбец первичного ключа, основанный на этом домене, и одновременно применяется ограничение PRIMARY KEY к этому столбцу: