Главная » Microsoft SQL Server, Базы данных » Создание первичных ключей

0

Первичные и внешние ключи являются теми нитями, которые связывают отдельные таблицы в единую реляционную базу данных. Я трактую эти столбцы как доменные, в отличие от пользовательских, которые хранят данные. Проектирование этих ключей имеет критическое влияние на производительность и полезность физической базы данных.

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

Первичные ключи

Реляционная база данных зависит от первичных ключей. Это краеугольный камень физической схемы базы данных. В среде разработчиков не утихают споры относительно использования естественных (понятных пользователям) и суррогатных (автоматически генерируемых) первичных ключей.

На физическом уровне первичные ключи служат двум целям:

?               для уникальной идентификации строк;

?               как объект ссылки для внешних ключей.

В SQL Server первичные и внешние ключи реализованы как ограничения. Задачей данных ограничений является гарантирование удовлетворения новыми данными определенных критериев или блокирование операций модификации данных.

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

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

?               С помощью объявления ограничения первичного ключа в инструкции CREATE TABLE. Следующий пример из учебной базы данных Cape Hatteras Adventures использует этот способ для создания таблицы Guide и назначения поля GuidelD первичным ключом с некластеризованным индексом:

CREATE TABLE dbo.Guide (

GuidelD INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,

LastName VARCHAR(50) NOT NULL,

FirstName VARCHAR(50) NOT NULL,

Qualifications VARCHAR(2048) NULL,

DateOfBirth DATETIME NULL,

DateHire DATETIME NULL )

ON [Primary];

?               С помощью объявления первичного ключа после создания таблицы. Для этого используется инструкция ALTER TABLE. Предполагая, что первичный ключ пока не назначен для таблицы Guide, следующая инструкция позволит это сделать:

ALTER TABLE dbo.Guide ADD CONSTRAINT PK_Guide PRIMARY KEY NONCLUSTERED(GuidelD)

ON [PRIMARY];

Метод индексации первичного ключа (кластеризованный и непастеризованный) является одним из самых важных соглашений проектирования физической схемы базы данных. В главе 50 мы углубимся в детали индексных страниц; там же вы узнаете и об основных стратегиях индексации первичных ключей.

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

Естественные первичные ключи

Естественные ключи отражают то, как объект идентифицируется в реальном мире. Фамилии людей, номера автомобилей и паспортов, а также адреса являются примерами естественных ключей.

Естественные ключи имеют свои достоинства и недостатки.

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

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

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

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

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

Использование столбцов идентичности в качестве суррогатных первичных ключей

Суррогатные ключи заполняются компьютером и, как правило, не имеют для людей никакого смыслового значения. В SQL Server суррогатные ключи представляют собой столбцы идентичности или глобальные уникальные идентификаторы.

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

?               Столбцы идентичности имеют два основных достоинства. Целые числа легче распознать на глаз и отредактировать, чем глобальные уникальные идентификаторы (GUID).

?               Целые числа невелики и быстро обрабатываются. Мое неформальное тестирование показало, что использование целых чисел на 10% повышает производительность по

сравнению с GUID. В других публикациях я встречал значения ускорения от 10 до 33%. Однако такой скачок производительности достигается только при обработке тысяч строк. Если инструкция SELECT отбирает из большой таблицы за одну операцию только несколько строк, то вы вряд ли заметите повышение производительности.

Отрицательные стороны использования столбцов идентичности приведены ниже.

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

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

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

Дополнительная Подробно о проблемах модификации данных в таблицах со столбцами иден- информация тичности см. в главе 16.

В следующем примере, взятом из базы данных Cape Hatteras Adventures, создается таблица, которая использует столбец идентичности для своего первичного ключа (листинг немного сокращен):

CREATE TABLE dbo.Event (

EventID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,

TourlD INT NOT NULL FOREIGN KEY REFERENCES dbo.Tour,

EventCode VARCHAR(10) NOT NULL,

DateBegin DATETIME NULL,

Comment NVARCHAR(255)

)

ON [Primary];

Установка столбца (или столбцов) в качестве первичного ключа в Management Studio не сложнее выделения этого столбца и щелчка на соответствующей кнопке панели инструментов. Для создания составного первичного ключа выберите все задействованные в нем столбцы и щелкните на кнопке панели инструментов.

Чтобы ознакомиться с обоими методами создания суррогатных ключей, обра- В титесь к учебным базам данных Family, Cape Hatteras Adventures и Materia! ^/ХСети Specification. Вышеназванные базы используют столбцы идентичности, а база ^    данных Outer Banks Kite Store использует глобальные уникальные идентифика

торы. Все фрагменты программ, приведенные в настоящей главе, можно загрузить с сайта книги по адресу www. SQLServerBible. com.

Использование уникальных идентификаторов в качестве суррогатных первичных ключей

Тип данных unique identifier в SQL Server является двойником глобального уникального идентификатора (GU1D), используемого в среде .NET. Это 16-байтовое шестнадцатеричное число, которое уникально для каждой таблицы, базы данных и сервера на всей планете. Хотя в пределах одного столбца уникальны и значения столбца идентичности, и значения

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

Идентификаторы GUID имеют ряд существенных достоинств.

?               База данных, использующая GUID в качестве первичных ключей, может быть легко реплицирована без каких-либо осложнений. Репликация добавит уникальные идентификаторы во все таблицы без столбца с типом uniqueidentif ier. Несмотря на то что это сделает столбцы глобально уникальными для задач репликации, код приложения будет продолжать идентифицировать строки только по целочисленным первичным ключам. Таким образом, слияние реплицированных строк в других серверах приведет к ошибке, так как будут встречаться дублирующиеся значения первичного ключа.

?               Использование GUTO отстраняет пользователя от смыслового значения первичного ключа.

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

?               Число глобальных уникальных идентификаторов бесконечно. Таблицы, созданные на основе столбца идентичности, могут содержать только 2147483648 строк. Естественно, тип данных можно установить в bigint или numeric, однако это лишит вас всех прелестей столбца идентичности.

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

Недостатки использования идентификаторов GUID в основном связаны с производительностью.

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

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

Таблица Product в базе данных Outer Banks Kite Store использует в качестве первичного ключа глобальный уникальный идентификатор. В следующем примере тип данных столбца ProductID устанавливается как uniqueidentif ier. В нем запрещаются пустые значения, а свойство rowguidcol устанавливается в значение True, что позволяет использовать этот столбец в репликациях. В качестве значения по умолчанию используется новый генерируемый идентификатор. Это поле используется в качестве первичного ключа, и для него создается некластеризованный индекс:

CREATE TABLE dbo.Product (

ProductID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL DEFAULT (NEWSEQUNTIALID())

PRIMARY KEY CLUSTERED,

ProductCategorylD UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES dbo.ProductCategory,

ProductCode CHAR(15) NOT NULL,

ProductName NVARCHAR(50) NOT NULL,

ProductDescription NVARCHAR(100) NULL,

ActiveDate DATETIME NOT NULL DEFAULT GETDATEO,

DiscountinueDate DATETIME NULL )

ON [Static];

Существуют два основных метода генерирования уникальных идентификаторов (на самом деле все они генерируются операционной системой Windows) и множество мест, где эта генерация может выполняться.

?               Функция NewID () генерирует уникальный идентификатор, используя несколько факторов, в том числе код NIC, адрес MAC, внутренний идентификатор процессора и текущий момент в часах компьютера. Последние шесть байтов занимает номер узла сетевой карты.

Универсальная функция NewID () может использоваться в качестве значения по умолчанию для столбца, передаваться в инструкцию INSERT, а также выполняться как функция в любом выражении.

?               Функция NewsequentionallD () аналогична функции NewID (), однако в отличие от последнего гарантирует, что каждое следующее значение уникального идентификатора больше всех остальных в таблице.

Функция NewsequentionallD () может использоваться только в качестве значения по умолчанию для столбца. И это имеет определенный смысл, так как генерируемое значение зависит от наибольшего идентификатора в конкретной таблице.

Функция NewsequentionallD () появилась в версии SQL Server 2005. Теперь уникальные идентификаторы могут быть кластеризованы без угрозы возникновения проблемы разбиения страниц.

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

На конференциях, посвященных SQL Server, я веду дискуссионные группы “Решения моделирования ключей данных: за и против”, посвященные рассмотренным в настоящем разделе вопросам. Предлагаю вам принять участие в этих диспутах, чтобы обсудить все возможные варианты решения задачи.

Создание внешних ключей

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

Допопнитешшая О целостности баз данных и принципах АСЮ вы можете подробно прочитать информация в главах 1 и 51.

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

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

Декларативная ссылочная целостность

Декларативная ссылочная целостность (DRI) в SQL Server может гарантировать ссылочную целостность без необходимости написания пользователем триггеров или процедур. Она обслуживается самим ядром базы данных и практически не загружает ресурсы компьютера (в отличие от триггера).

В SQL Server декларативная ссылочная целостность поддерживается с помощью ограничений внешних ключей. Вы можете открыть форму Foreign Key Relationships, показанную на рис. 17.6, чтобы создать или изменить ограничения внешних ключей. Для этого выполните следующее.

?               В конструкторе баз данных выделите столбец первичного ключа и перетащите его к столбцу внешнего ключа. Это приведет к открытию диалогового окна Foreign Key Relationships.

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

?               В конструкторе таблиц щелкните на кнопке Relationships панели инструментов или выберите в меню команду Tabled Designers Relationships. В качестве альтернативы можете выбрать в конструкторе баз данных вторую таблицу (с внешним ключом), а затем щелкнуть на кнопке Relationships панели инструментов или выбрать одноименный пункт из контекстного меню этой таблицы.

Рис. 17.6. Форма Foreign Key Relationships утилиты Management Studio используется для создания и модификации декчаративной ссылочной целостности

?               Enforce for Replications (поддерживать репликации).

?               Enforce Foreign Key Constraint (поддерживать ограничения внешнего ключа).

?               Delete Rule (каскадное удаление мы рассмотрим немного позже в этой главе).

?               Update Rule (обновить правило).

В коде Т-SQL вы можете объявить ограничения внешнего ключа либо при создании таблиц, либо после этого. После определения столбца фраза FOREIGN KEY REFERENCE, за которой следует имя таблицы (и при необходимости — столбца), позволяет создать внешний ключ, например:

ForeignKeyColumn FOREIGN KEY REFERENCES PrimaryTable(PKID)

В следующем примере, взятом из учебной базы данных СНА, создается связующая таблица tour_mm_guide, разрешающая отношение “многие ко многим”. Эта таблица имеет два внешних ключа: по одному для таблиц Tour и Guide. Внешний ключ Tour ID явно ссылается на столбец первичного ключа, а внешний ключ GuidelD указывает на таблицу и использует первичный ключ по умолчанию:

CREATE TABLE dbo.Tour_mm_Guide (

TourGuidelD INT IDENTITY NOT NULL

PRIMARY KEY NONCLUSTERED,

TourlD INT NOT NULL

FOREIGN KEY REFERENCES dbo.Tour(TourID)

ON DELETE CASCADE,

GuidelD INT NOT NULL

FOREIGN KEY REFERENCES dbo.Guide ON DELETE CASCADE,

QualDate DATETIME NOT NULL,

RevokeDate DATETIME NULL )

ON [Primary];

Некоторые проектировщики баз данных предпочитают включать ограничения внешних ключей в определения таблиц, другие добавляют их уже после создания таблиц. Если таблица уже существует, то вы можете создать ограничение первичного ключа, используя инструкцию ALTER TABLE ADD CONSTRAINT:

ALTER TABLE SecondaryTableName ADD CONSTRAINT ConstraintName FOREIGN KEY (ForeignKeyColumns)

REFERENCES dbo.PrimaryTable (PrimaryKeyColumnName);

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

В следующем примере, скопированном из файла f amily_create. sql, создается таблица Person, после чего настраиваются внешние ключи MotherlD и FatherlD:

CREATE TABLE dbo.Person (

PersonID INT NOT NULL PRIMARY KEY NONCLUSTERED,

LastName VARCHAR(15) NOT NULL,

FirstName VARCHAR(15) NOT NULL,

SrJr VARCHAR(3) NULL,

MaidenName VARCHAR(15) NULL,

Gender CHAR(l) NOT NULL,

FatherlD INT NULL,

MotherlD INT NULL,

DateOfBirth DATETIME NULL,

DateOfDeath DATETIME NULL ) ;

ALTER TABLE dbo.Person

ADD CONSTRAINT FK_Person_Father

FOREIGN KEY(FatherlD) REFERENCES dbo.Person (PersonID);

ALTER TABLE dbo.Person

ADD CONSTRAINT FK_Person_Mother

FOREIGN KEY(MotherlD) REFERENCES dbo.Person (PersonID);

Для получения списка внешних ключей текущей базы данных программным путем выполните запрос к представлению каталога sysforeign_key_columns.

Дополнительные внешние ключи

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

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

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

Каскадные удаления и обновления

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

Решение этой проблемы сводится к модификации строк вторичной таблицы как части транзакции, изменяющей строки первичной таблицы. Декларативная ссылочная целостность может выполнить эту работу вместо вас. Для строк вторичной таблицы возможны четыре варианта действий, выбираемых в параметрах Delete Rule и Update Rule формы Foreign Key Relationships. Параметр Update Rule имеет смысл только для естественных первичных ключей.

?               No Action. Строки вторичной таблицы не изменяются. Их присутствие будет предохранять строки первичной таблицы от удаления и модификации.

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

связанные с ними вторичные. Например, если для какого-то клиента выписан счет- фактура, вы не сможете его удалить.

?               Cascade. Операции удаления и модификации, выполняемые на первичных строках, выполняются также и на вторичных.

Используйте этот вариант, когда вторичные данные теряют смысл в отсутствие первичных. Например, если удаляется заказ с номером 123, все его строки будут также удаляться. Если номер заказа изменяется с 123 на 456, то его строки также должны изменить номер заказа на это значение (предполагается, что используется естественный первичный ключ).

?               Set Null. Этот вариант оставляет вторичные строки нетронутыми, однако изменяет значение вторичного ключа на пустое (разумеется, предполагается допустимость наличия пустых значений).

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

?               Set Default. Первичные строки могут удаляться и изменяться, при этом значения внешнего ключа изменяются на значения по умолчанию, заданные для данного столбца.

Этот вариант аналогичен варианту Set Null, за исключением того, что на этот раз внешнему ключу присваивается определенное значение. В схемах, использующих суррогатные пустые значения (например, пустые строки), установка значений столбца по умолчанию и определение правила удаления как Set Default приведет к занесению во внешний ключ пустой строки при удалении строки первичного ключа.

Дополнительная О каскадных удалениях, а также о сопровождающих их проблемах см. в разде- информация ле “Удаление данных” главы 16.

В коде Т-SQL добавление параметра ON DELETE CASCADE в ограничение внешнего ключа форсирует каскадные операции. В следующем примере, взятом из базы данных OBXKites, таблица строк заказа OrderDetail использует параметр каскадного удаления в ограничении внешнего ключа OrderlD:

CREATE TABLE dbo.OrderDetail (

OrderDetailID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL DEFAULT (NEWID())

PRIMARY KEY NONCLUSTERED,

OrderlD UNIQUEIDENTIFIER NOT NULL

FOREIGN KEY REFERENCES dbo.[Order]

ON DELETE CASCADE,

ProductID UNIQUEIDENTIFIER NULL

FOREIGN KEY REFERENCES dbo.Product,

Дополнительная В главе 24, посвященной расширенным решениям на языке Т-SQL, будет пока- информация зано, как создать триггеры, обеспечивающие ссылочную целостность, и каскадные удаления в нестандартных схемах данных в пределах одной или нескольких баз данных.

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии