Створення первинних ключів

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

Схема бази даних повинна бути трансформована з теоретичної логічної структури у фізичну, і в основі останньої часто лежать первинні та зовнішні ключі Після того як база даних буде запущена в виробничих умовах, ключі вже складно змінити Правильний вибір первинних ключів на етапі проектування – це те, за що варто боротися

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

Реляційна база даних залежить від первинних ключів Це наріжний камінь фізичної схеми бази даних У середовищі розробників не вщухають суперечки щодо використання природних (зрозумілих користувачам) і сурогатних (автоматично генеруються) первинних ключів

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

■ для унікальної ідентифікації рядків

■ як обєкт посилання для зовнішніх ключів

У SQL Server первинні та зовнішні ключі реалізовані як обмеження Завданням даних обмежень є гарантування задоволення новими даними певних критеріїв або блокування операцій модифікації даних

Обмеження первинного ключа є комбінацією вимог унікальності (що передбачає і заборону порожніх значень) та використання унікального індексу (кластеризованого чи ні)

У програмі стовпець може бути призначений первинним ключем двома способами

■ За допомогою оголошення обмеження первинного ключа в інструкції CREATE TABLE Наступний приклад з навчальної бази даних Cape Hatteras Adventures використовує цей спосіб для створення таблиці Guide і призначення поля GuidelD первинним ключем з некластерізованний індексом:

CREATE TABLE dboGuide (

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 dboGuide 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 dboEvent (

EventID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,

TourlD INT NOT NULL FOREIGN KEY REFERENCES dboTour,

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 dboProduct (

ProductID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL DEFAULT (NEWSEQUNTIALID())

PRIMARY KEY CLUSTERED,

ProductCategorylD UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES dboProductCategory,

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, показану на рис 176, щоб створити або змінити обмеження зовнішніх ключів Для цього виконаєте наступне

■ У конструкторі баз даних виділіть стовпець первинного ключа і перетягніть його до колонку зовнішнього ключа Це призведе до відкриття діалогового вікна Foreign Key Relationships

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

■ У конструкторі таблиць клацніть на кнопці Relationships панелі інструментів або виберіть у меню команду Tabled Designers Relationships Як альтернатива можете вибрати в конструкторі баз даних другу таблицю (з зовнішнім ключем), а потім клацніть на кнопці Relationships панелі інструментів або вибрати однойменний пункт з контекстного меню цієї таблиці

Рис 176 Форма 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 dboTour_mm_Guide (

TourGuidelD INT IDENTITY NOT NULL

PRIMARY KEY NONCLUSTERED,

TourlD INT NOT NULL

FOREIGN KEY REFERENCES dboTour(TourID)

ON DELETE CASCADE,

GuidelD INT NOT NULL

FOREIGN KEY REFERENCES dboGuide 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 dboPrimaryTable (PrimaryKeyColumnName)

Таблиця Person бази даних Family повинна використовувати цей метод, оскільки використовує рекурсивне ставлення Зовнішній ключ не може бути створений раніше відповідного первинного ключа Так як рекурсивне ставлення посилається на ту ж таблицю, вона повинна бути створена раніше зовнішнього ключа

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

CREATE TABLE dboPerson (

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 dboPerson

ADD CONSTRAINT FK_Person_Father

FOREIGN KEY(FatherlD) REFERENCES dboPerson (PersonID)

ALTER TABLE dboPerson

ADD CONSTRAINT FK_Person_Mother

FOREIGN KEY(MotherlD) REFERENCES dboPerson (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 dboOrderDetail (

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 dboProduct,

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

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


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

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*