P rimary Key (Первинний ключ) є полем у таблиці, яке однозначно ідентифікує кожен рядок/запис у таблиці бази даних. Первинні ключі повинні мати унікальні значення. Первинний ключ стовпець не може мати значення.
Таблиця може мати лише один первинний ключ, який може складатися з одного або кількох полів. Коли кілька полів використовуються як первинний ключ, їх називають складовим ключем.
Якщо таблиця має первинний ключ, визначений будь-якому полі (ях), ви можете мати дві записи, мають однакове значення цього поля (ей).
Примітка- Ви могли б використовувати ці поняття під час створення таблиць бази даних.
Створення первинного ключа
Ось синтаксис для визначення атрибута ID як первинний ключ у таблиці Customers.
CREATE TABLE CUSTOMERS(ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID));
Для того, щоб створити обмеження первинного ключа на стовпці ID, коли таблиця CUSTOMERS вже існує, використовуйте наступний синтаксис SQL:
ALTER TABLE CUSTOMERS ADD PRIMARY KEY (ID);
Примітка
Якщо ви використовуєте оператор ALTER TABLE, щоб додати первинний ключ, стовпець первинного ключа (їй) повинен був вже оголошений як значення, що не містить NULL (якщо таблиця була створена першим).
Для визначення первинного ключа на кількох стовпцях, використовуйте синтаксис SQL, наведений нижче:
CREATE TABLE CUSTOMERS(ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID, NAME));
Щоб створити обмеження первинного ключа на колонки ID і NAME, коли таблиця CUSTOMERS вже існує, використовуйте наступний синтаксис SQL.
ALTER TABLE CUSTOMERS ADD CONSTRAINT PK_CUSTID PRIMARY KEY (ID, NAME);
Видалення первинного ключа
Ви можете очистити обмеження первинного ключа з таблиці за допомогою синтаксису, наведеного нижче.
ALTER TABLE CUSTOMERS DROP PRIMARY KEY;
Останнє оновлення: 27.04.2019
Зовнішні ключі дозволяють встановити зв'язок між таблицями. Зовнішній ключ встановлюється для стовпців із залежної, підлеглої таблиці, і вказує на один із стовпців із головної таблиці. Як правило, зовнішній ключ вказує на первинний ключ із пов'язаної головної таблиці.
Загальний синтаксис встановлення зовнішнього ключа на рівні таблиці:
FOREIGN KEY (стовпець1, стовпець2, ... стовпецьN) REFERENCES головна_таблиця (стовпець_головної_таблиці1, стовпець_головної_таблиці2, ... стовпець_головної_таблиціN)
Для створення обмеження зовнішнього ключа після FOREIGN KEY вказується стовпець таблиці, який представляє зовнішній ключ. А після ключового слова REFERENCES вказується ім'я пов'язаної таблиці, а потім у дужках ім'я зв'язаного стовпця, на який вказуватиме зовнішній ключ. Після виразу REFERENCES йдуть вирази ON DELETE та ON UPDATE , які задають дію під час видалення та оновлення рядка з головної таблиці відповідно.
Наприклад, визначимо дві таблиці та зв'яжемо їх за допомогою зовнішнього ключа:
CREATE TABLE Customers (Id INT PRIMARY KEY AUTO_INCREMENT, Age INT, FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(20) NOT NULL, Phone VARCHAR(20) NOT NULL UNIQUE); CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id));
В даному випадкувизначено таблиці Customers та Orders. Customers є головною і представляє клієнта. Orders є залежною та представляє замовлення, зроблене клієнтом. Таблиця Orders через стовпець CustomerId пов'язана з таблицею Customers та її стовпцем Id. Тобто стовпець CustomerId є зовнішнім ключем, який свідчить про стовпець Id з таблиці Customers.
За допомогою оператора CONSTRAINT можна встановити ім'я для обмеження зовнішнього ключа:
CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, CONSTRAINT orders_custonmers_fk FOREIGN KEY (CustomerId) REFERENCES Customers (Id));
ON DELETE та ON UPDATE
За допомогою виразів ON DELETE та ON UPDATE можна встановити дії, які виконуються відповідно при видаленні та зміні зв'язаного рядка з головної таблиці. Як дія можуть використовуватися такі опції:
CASCADE : автоматично видаляє або змінює рядки із залежної таблиці при видаленні або зміні зв'язаних рядків у головній таблиці.
SET NULL: при видаленні або оновленні зв'язаного рядка з головної таблиці встановлює значення NULL для стовпця зовнішнього ключа. (У цьому випадку стовпець зовнішнього ключа має підтримувати установку NULL)
RESTRICT : відхиляє видалення або зміну рядків у головній таблиці за наявності пов'язаних рядків у залежній таблиці.
NO ACTION : те саме, що й RESTRICT .
SET DEFAULT : при видаленні зв'язаного рядка з головної таблиці встановлює для стовпчика зовнішнього ключа значення за промовчанням, яке задається за допомогою атрибута DEFAULT. Незважаючи на те, що ця опція в принципі доступна, проте двигун InnoDB не підтримує цей вираз.
Каскадне видалення
Каскадне видалення дозволяє при видаленні рядка з головної таблиці автоматично видалити всі пов'язані рядки із залежної таблиці. Для цього застосовується опція CASCADE:
CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE);
Подібним чином працює і вираз ON UPDATE CASCADE. При зміні первинного ключа автоматично зміниться значення пов'язаного з ним зовнішнього ключа. Однак оскільки первинні ключі змінюються дуже рідко, та й з принципу не рекомендується використовувати як первинні ключі стовпці з змінними значеннями, то практично вираз ON UPDATE використовується рідко.
Встановлення NULL
При установці для зовнішнього ключа опції SET NULL необхідно, щоб стовпець зовнішнього ключа допускав значення NULL:
CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL);
У цій статті ми спробуємо розглянути все, що стосується ключів у SQL:навіщо потрібні, створення, обмеження ключів. Загалом: буде нудно 😉
План на сьогодні такий:
Теоретично реляційних баз даних ключіце деякі сутності, створені задля встановлення певних обмежень, які підтримують цілісність і доступність даних у таблицях баз даних.
Якщо казати простими словами, то ключі в sqlстворено для того, щоб вказати додаткову функціональність стовпця. Чи це унікальність або те, що стовпець посилається на іншу таблицю (зовнішній ключ).
Первинний ключ
Стовпець, який у базі даних повинен бути унікальним позначають первинним ключем. Первинний ключ або primary key означає, що у таблиці значення колонки primary key неспроможна повторюватися. Таким чином даний ключдозволяє однозначно ідентифікувати запис у таблиці не боячись у своїй, що значення стовпця повторитися. Відразу приклад: припустимо, у Вас є таблиця користувачів. У цій таблиці є поля: ПІБ, рік народження, телефон. Як ідентифікувати користувача? Таким параметрам як ПІБ та телефон довіряти не можна. Адже у нас може бути кілька користувачів не лише з однаковим прізвищем, але й з ім'ям. Телефон може змінюватися з часом і користувач з номером телефону може виявитися не тим, хто у нас у базі даних.
Ось для цього і вигадали первинний ключ. Один раз надали унікальний ідентифікатор і все. В mySqlна прикладі якої ми виконуємо всі приклади з поля AUTO_INCREMENTне можна задати, якщо не вказати, що це первинний ключ.
Думаю, що не варто згадувати, що поле позначене як первинний ключ не може бути порожнім під час створення запису.
Зовнішній ключ ( foreign key)
Є ще зовнішній ключ (foreign key). Його ще називають посилальним. Він необхідний зв'язування таблиць між собою.
Якщо подивитися на картинку вище, то зовнішнім ключем буде поле постачальника в таблиці взуття. Як правило, при створенні таблиці задають колонку унікальних цілісних значень. Як ми це робили, коли створювали таблицю supplier.
Колонка supplier_idбуде унікальним для кожного запису. Її значення і виступатиме на помсті колонки постачальникв таблиці взуття.Пропоную відразу і розглянути на прикладі, як створюється зовнішній ключ.
Створення зовнішнього ключа
create table shoes(shoes_id int auto_increment primary key, title text, size int, price float, count int, type varchar(30), supplier int, foreign key (supplier) references supplier (supplier_id));
Як видно на прикладі вище, синтаксис створення зовнішнього ключа досить простий. Потрібно до таблиці додати поле, а потім оголосити це поле як зовнішній ключ і вказати, куди він посилатиметься. В даному випадку поле supplierбуде посилатися на поле supplier_idв таблиці supplier.
Складовий ключ (composite key)
Щодо складеного ключа — це кілька первинних ключів у таблиці. Таким чином, створивши composite key, унікальність запису буде перевірятись по полях, які об'єднані в цей ключ.
Бувають ситуації, коли при вставці таблицю потрібно перевіряти запис на унікальність відразу по кількох полях. Ось для цього і вигадано складовий ключ. Для прикладу я створю просту таблицю з composite key , щоб показати синтаксис:
Create table test(field_1 int, field_2 text, field_3 bigint, primary key (field_1, field_3));
У прикладі вище два поля об'єднані в складовий ключ і таблиці не буде записів з цими однаковими полями.
Це все, що стосується ключів у SQL.Це невеликий посібник — підготовка до статті, де докладно розглянемо, як об'єднувати таблиці, щоб вони становили єдину базу даних.
У процесі проектування бази даних приймається рішення, які таблиці повинні входити до бази даних, які будуть імена (ідентифікатори), які типи даних знадобляться побудови таблиць і які користувачі отримають доступом до кожної їх. Крім того, для ефективного створення таблиць необхідно відповісти на такі запитання:
- Стовпці якого типу та розміру будуть становити кожну таблицю, які потрібно вибрати імена для стовпців таблиць?
- Які стовпці можуть містити значення NULL?
- Чи будуть використані обмеження цілісності, значення за замовчуванням та правила для стовпців?
- Чи потрібне індексування стовпців, які типи індексів будуть застосовані для конкретних стовпців?
- Які стовпці входитимуть у первинні та зовнішні ключі.
Для створення таблиць у середовищі MS SQL Serverвикористовується команда:
<определение_таблицы>::= CREATE TABLE [ ім'я_бази_даних.[власник]. | власник. ]ім'я_таблиці (<элемент_таблицы>[,...n])
<элемент_таблицы> ::= {<определение_столбца>} | <имя_столбца>AS<выражение> | <ограничение_таблицы>
Зазвичай, власником таблиці (dbo) є той, хто її створив.
<Выражение>задає значення для обчислюваного стовпця. Обчислювані стовпці- це віртуальні стовпці, т. е. фізично таблиці де вони зберігаються і обчислюються з допомогою значень стовпців тієї ж таблиці. У виразі для обчислюваного стовпцяможуть бути імена звичайних стовпців, константи та функції, пов'язані одним або декількома операторами. Підзапити в такому вираженні брати участь не можуть. Обчислювані стовпціможуть бути включені до розділу SELECT при вказівці списку стовпців, які мають бути повернені в результаті виконання запиту. Обчислювані стовпціне можуть входити до зовнішнього ключа, для них не використовуються значення за замовчуванням. Крім того, обчислювані стовпціне можуть брати участь в операціях INSERT та DELETE.
<определение_столбца>::= ( ім'я_стовпця<тип_данных>) [ [ DEFAULT<выражение>] | [ IDENTITY (початок, крок) ]]] [<ограничение_столбца>][...n]]
У визначенні стовпця звернемо увагу на параметр IDENTITY , який вказує, що відповідний стовпець буде стовпцем-лічильником. Для таблиці може бути визначений лише один стовпець із такою властивістю. Можна додатково вказати початкове значення та крок збільшення. Якщо ці значення не вказуються, то за умовчанням вони обидва рівні 1. Якщо з ключовим словом IDENTITY вказано NOT FOR REPLICATION , то сервер не виконуватиме автоматичного генерування значень для цього стовпця, а дозволить вставку в стовпець довільних значень.
Як обмеження використовуються обмеження стовпцяі обмеження таблиці. Відмінність між ними в тому, що обмеження стовпцязастосовується тільки до певного поля, а обмеження таблиці- До груп з одного або більше полів.
<ограничение_столбца>::= [ CONSTRAINT ім'я_обмеження ] ( [ NULL | NOT NULL ] | [ (PRIMARY KEY | UNIQUE ) [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR=фактор_заповнення ] [ ON (ім'я_групи_файлів | DEFAULT ) ] Назва_рід_таблиці [(ім'я_стовпця_рід_таблиці) ] [ ON DELETE ( CASCADE | NO ACTION ) ] [ ON UPDATE ( CASCADE | NO ACTION ) ] [ NOT FOR REPLICATION ]] | CHECK [ NOT FOR REPLICATION](<лог_выражение>) } <ограничение_таблицы>::= ( [ ( PRIMARY KEY | UNIQUE ) [ CLUSTERED | NONCLUSTERED ] ((ім'я_стовпця [,...n])) ] |FOREIGN KEY[(ім'я_стовпця [,...n])] REFERENCES ім'я_рід_таблиці [(ім'я_стовпця_ ,...n])] [ ON DELETE ( CASCADE | NO ACTION ) ] [ ON UPDATE ( CASCADE | NO ACTION ) ] | NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ]
Розглянемо окремі параметри представлених конструкцій, пов'язані з обмеженнями цілісності даних. Обмеження цілісностімають пріоритет над тригерами, правилами та значеннями за умовчанням. До обмеженням цілісностівідносяться обмеження первинного ключа PRIMARY KEY, обмеження зовнішнього ключа FOREIGN KEY, обмеження унікальності UNIQUE, обмеження значення NULL, обмеження на перевірку CHECK.
Обмеження первинного ключа (PRIMARY KEY)
Таблиця зазвичай має стовпець або комбінацію стовпців, значення яких унікально ідентифікують кожен рядок таблиці. Цей стовпець (або стовпці) називається первинним ключемтаблиці і необхідний забезпечення її цілісності. Якщо первинний ключ входить більше одного стовпця, то значення в межах одного стовпця можуть дублюватися, але будь-яка комбінація значень всіх стовпців первинного ключамає бути унікальною.
При створенні первинного ключа SQL Server автоматично створює унікальний індекс для стовпців, що входять до первинного ключа . Він прискорює доступ до даних цих стовпців під час використання первинного ключау запитах.
Таблиця може мати лише одне обмеження PRIMARY KEY, причому жоден із включених у первинний ключ стовпців не може набувати значення NULL. При спробі використовувати як первинного ключастовпець (або групу стовпців), для якого обмеження первинного ключане виконуються, первинний ключ не створено, а система видасть повідомлення про помилку.
Оскільки обмеження PRIMARY KEY гарантує унікальність даних, воно часто визначається для стовпців-лічильників. створіння обмеження цілісності PRIMARY KEY можливе як при створенні, так і при зміні таблиці. Одним із призначень первинного ключає забезпечення посилальної цілісностіданих кількох таблиць. Звичайно, це може бути реалізовано лише при визначенні відповідних зовнішніх ключів в інших таблицях.
Обмеження зовнішнього ключа (FOREIGN KEY)
Обмеження зовнішнього ключа- це основний механізм підтримки посилальної цілісностіміж таблицями реляційної бази даних Стовпець дочірньої таблиці, визначений як зовнішній ключ у параметрі FOREIGN KEY , застосовується посилання на стовпець батьківської таблиці, що у ній первинним ключем. Ім'я батьківської таблиці та стовпці її первинного ключавказуються у реченні REFERENCES . Дані в стовпцях, визначених як зовнішній ключ, можуть приймати тільки такі ж значення, які знаходяться у пов'язаних з ним стовпцях первинного ключабатьківської таблиці. Збіг імен стовпців для зв'язку дочірньої та батьківської таблиць необов'язковий. Первинний ключ може бути визначений для стовпця з одним ім'ям, у той час як стовпець, на який накладено обмеження FOREIGN KEY може мати зовсім інше ім'я. Єдиною вимогою залишається відповідність стовпців за типом та розміром даних.
На первинний ключ можуть посилатися як стовпці інших таблиць, а й стовпці, розташовані у тій самій таблиці, як і власне первинний ключ ; це дозволяє створювати рекурсивні структури.
Зовнішній ключ може бути пов'язаний не тільки з первинним ключеміншої таблиці. Він може бути визначений і для стовпців з обмеженням UNIQUE другої таблиці або будь-яких інших стовпців, але таблиці повинні бути в одній базі даних.
Стовпці зовнішнього ключа можуть містити значення NULL, проте ігнорується перевірка на обмеження FOREIGN KEY. Зовнішній ключ може бути проіндексований, тоді сервер буде швидше шукати необхідні дані. Зовнішній ключ визначається як при створенні, так і при зміні таблиць.
Обмеження посилальної цілісностізадає вимога, згідно з яким для кожного запису в дочірній таблиці повинен бути запис батьківської таблиці. При цьому зміна значення стовпця зв'язку в записі батьківської таблиці за наявності дочірнього запису блокується, так само як і видалення батьківського запису (заборона каскадної зміни та видалення), що гарантується параметрами ON DELETE NO ACTION та ON UPDATE NO ACTION , прийнятими за умовчанням. Для дозволу каскадного впливу слід використовувати параметри ON DELETE CASCADE та ON UPDATE CASCADE.
