Це електронні сховища інформації, доступ до яких здійснюється за допомогою одного або кількох комп'ютерів. Зазвичай БД створюються для зберігання та доступу до даних, що містить відомості про деяку предметну область, тобто деяку область людської діяльності або частини реального світу.
СУБД – це програмні засобидля створення, наповнення, оновлення та видалення БД.
Одиницею інформації, що зберігається в БД, є таблиця. Кожна таблиця є сукупністю рядків і стовпців, де рядки відповідають екземпляру об'єкта, конкретної події чи явищу, а стовпці – атрибутам (ознакам, характеристикам, параметрам) об'єкта, події чи явища. Кожен рядок містить інформацію про конкретну подію.
У термінах БД стовпці таблиці називаються полями, та її рядки – записами.
Між окремими таблицями БД можуть існувати зв'язки, тобто інформація попередньої таблиці може додаватися інший. БД, між окремими таблицями яких є зв'язку, називаються реляційними. Одна і та сама таблиця може бути головною по відношенню до однієї таблиці БД і дочірньої по відношенню до іншої.
Пов'язані відносинами таблиці взаємодіють за принципом головна-підлегла. Одна і та сама таблиця може бути головною до однієї таблиці БД і дочірньої до іншої.
Об'єкт - Це щось існуюче і помітне, що має набір властивостей. Відмінність одного об'єкта з іншого об'єкта визначається конкретними значеннями властивостей.
Сутність - Відображення об'єкта в пам'яті людини або комп'ютера.
Атрибут - Конкретне значення будь-якої з властивостей сутності.
Поле – це один елемент запису, де зберігається конкретне значення атрибута.
Поле зв'язку – це поле, яким дві таблиці пов'язані.
Первинні та вторинні ключі
У кожній таблиці БД може існувати первинний ключ – це поле або табір полів, що однозначно ідентифікує запис.
Значення первинного ключа у таблиці БД має бути унікальним, тобто у таблиці не повинно існувати двох мул більше записів з однаковим значенням первинного ключа.
Первинні ключі полегшують встановлення зв'язків між таблицями. Оскільки первинний ключ має бути унікальним, йому можуть використовуватися в повному обсязі поля таблиці.
Якщо таблиці немає полів, значення яких унікально, до створення первинного ключа у ній зазвичай вводять додаткове числове поле, значеннями якого СУБД може розпоряджатися на власний розсуд.
Вторинні ключі встановлюються за полем, які часто використовуються при пошуку або сортування даних: побудовані за вторинними ключами індекси допоможуть системі значно швидше знайти потрібні значення, що зберігаються у відповідних полях.
На відміну від первинних ключів поля для вторинних ключів можуть містити не унікальну інформацію.
Реляційні відносини між таблицями
Один до одного. Ставлення один до одного має місце, коли одному запису в батьківській таблиці відповідає один запис в дочірній таблиці.
Дане відношення зустрічається набагато рідше, ніж відношення одним-багатьом, його використовують, якщо не хочуть, щоб таблиця БД розпухла від другорядної таблиці. Зв'язок один до одного призводить до того, що для читання пов'язаної інформації в декількох таблицях, доводиться виробляти кілька операцій читання, що уповільнює отримання потрібної інформації. Крім того БД, до складу яких входять таблиці зі зв'язком один до одного не можуть вважатися повністю нормалізованими.
Подібно до зв'язку одним, зв'язок один до одного може бути жорстким і нежорстким.
Останнє оновлення: 02.07.2017
Бази даних можуть містити таблиці, пов'язані між собою різними зв'язками. Зв'язок (relationship) є асоціацією між сутностями різних типів.
При виділенні зв'язку виділяють головну або батьківську таблицю (primary key table/master table) та залежну, дочірню таблицю (foreign key table/child table). Дочірня таблиця залежить від батьківської.
Для організації зв'язку застосовуються зовнішні ключі. Зовнішній ключ представляє один або кілька стовпців однієї таблиці, який одночасно є потенційним ключем з іншої таблиці. Зовнішній ключ необов'язково має відповідати первинному ключу головної таблиці. Хоча, як правило, зовнішній ключ із залежної таблиці вказує на первинний ключ із головної таблиці.
Зв'язки між таблицями бувають наступних типів:
Один до одного (One to one)
Один до багатьох (One to many)
Багато хто до багатьох(Many to many)
Зв'язок один до одного
Цей тип зв'язків зустрічає не часто. І тут об'єкту однієї сутності можна порівняти лише одне об'єкт інший сутності. Наприклад, на деяких сайтах користувач може мати лише один блог. Тобто виникає відношення один користувач – один блог.
Нерідко цей тип зв'язків передбачає розбиття однієї великої таблиці кілька маленьких. Основна батьківська таблиця в цьому випадку продовжує містити дані, що часто використовуються, а дочірня залежна таблиця зазвичай зберігає дані, які використовуються рідше.
Щодо цього первинний ключ залежної таблиці в той же час є зовнішнім ключем, який посилається на первинний ключ із головної таблиці.
Наприклад, таблиця Users представляє користувачів і має такі стовпці:
UserId (ідентифікатор, первинний ключ)
Name (ім'я користувача)
І таблиця Blogs представляє блоги користувачів і має такі стовпці:
BlogId (ідентифікатор, первинний та зовнішній ключ)
Name (назва блогу)
У цьому випадку стовпець BlogId зберігатиме значення зі стовпця UserId з таблиці користувачів. Тобто стовпець BlogId виступатиме одночасно первинним та зовнішнім ключем.
Зв'язок один до багатьох
Це найпоширеніший тип зв'язків. У цьому типі зв'язків кілька рядків із дочірньої таблиці залежать від одного рядка у батьківській таблиці. Наприклад, в одному блогу може бути кілька статей. У цьому випадку таблиця блогів є батьківською, а таблиця статей – дочірньою. Тобто один блог – багато статей. Або інший приклад у футбольній команді може грати кілька футболістів. І водночас один футболіст одночасно може грати лише в одній команді. Тобто одна команда – багато футболістів.
Наприклад, нехай буде таблиця Articles, яка представляє статті блогу і яка має такі стовпці:
ArticleId (ідентифікатор, первинний ключ)
BlogId (зовнішній ключ)
Title (назва статті)
Text (текст статті)
У цьому випадку стовпець BlogId з таблиці статей зберігатиме значення зі стовпця BlogId з таблиці блогів.
Зв'язок багато хто до багатьох
При цьому типі зв'язків один рядок з таблиці А може бути пов'язаний з безліччю рядків з таблиці В. У свою чергу один рядок з таблиці А може бути пов'язаний з безліччю рядків з таблиці А. Типовий приклад - студенти та курси: один студент може відвідувати кілька курсів і відповідно на один курс можуть записатися кілька студентів.
Інший приклад - статті та теги: для однієї статті можна визначити кілька тегів, а один тег може бути визначений для кількох статей.
Але в SQL Serverна рівні бази даних ми не можемо встановити прямий зв'язок до багатьох між двома таблицями. Це робиться у вигляді допоміжної проміжної таблиці. Іноді дані цієї проміжної таблиці представляють окрему сутність.
Наприклад, у випадку зі статтями та тегами нехай буде таблиця Tags, яка має два стовпці:
TagId (ідентифікатор, первинний ключ)
Text (текст тега)
Також нехай буде проміжна таблиця ArticleTags з наступними полями:
TagId (ідентифікатор, первинний та зовнішній ключ)
ArticleIdId (ідентифікатор, первинний та зовнішній ключ)
Технічно ми отримаємо два зв'язки одним-багатьом. Стовпець TagId із таблиці ArticleTags буде посилатися на стовпець TagId із таблиці Tags. А стовпець ArticleId із таблиці ArticleTags буде посилатися на стовпець ArticleId із таблиці Articles. Тобто стовпці TagId та ArticleId у таблиці ArticleTags представляють складовий первинний ключ і є зовнішніми ключами для зв'язку з таблицями Articles і Tags.
Посилальна цілісність даних
При зміні первинних і зовнішніх ключів слід дотримуватися такого аспекту як посилальна цілісність даних(Referential integrity). Її основна ідея полягає в тому, щоб дві таблиці в базі даних, які зберігають ті самі дані, підтримували їх узгодженість. Цілісність даних представляє правильно збудовані відносини між таблицями з коректною установкоюпосилань між ними. У яких випадках цілісність даних може порушуватися:
Аномалія видалення(Deletion anomaly). Виникає у разі видалення рядка з головної таблиці. У цьому випадку зовнішній ключ із залежної таблиці продовжує посилатися на віддалений рядок із головної таблиці
Аномалія вставки(Insertion anomaly). Виникає при вставці рядка у залежну таблицю. У цьому випадку зовнішній ключ із залежної таблиці не відповідає первинному ключу жодного з рядків із головної таблиці.
Аномалії оновлення(Update аномалі). При подібній аномалії кілька рядків однієї таблиці можуть містити дані, що належать тому самому об'єкту. При зміні даних в одному рядку вони можуть суперечити даними з іншого рядка.
Аномалія видалення
Для вирішення аномалії видалення зовнішнього ключа слід встановлювати одне з двох обмежень:
Якщо рядок із залежної таблиці обов'язково вимагає наявності рядка із головної таблиці, то для зовнішнього ключа встановлюється каскадне видалення. Тобто при видаленні рядка із головної таблиці відбувається видалення зв'язаного рядка (рядків) із залежної таблиці.
Якщо рядок із залежної таблиці допускає відсутність зв'язку зі рядком із головної таблиці (тобто такий зв'язок необов'язковий), то для зовнішнього ключа при видаленні зв'язаного рядка із головної таблиці задається встановлення значення NULL. При цьому стовпець зовнішнього ключа має допускати значення NULL.
Аномалія вставки
Для вирішення аномалії вставки при додаванні до залежної таблиці даних стовпець, який представляє зовнішній ключ, повинен допускати значення NULL. І таким чином, якщо об'єкт, що додається, не має зв'язку з головною таблицею, то в стовпці зовнішнього ключа стоятиме значення 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.Це невеликий посібник — підготовка до статті, де докладно розглянемо, як об'єднувати таблиці, щоб вони становили єдину базу даних.
Останнє оновлення: 09.07.2017
Зовнішні ключі використовуються для встановлення зв'язку між таблицями. Зовнішній ключ встановлюється для стовпців із залежної, підлеглої таблиці, і вказує на один із стовпців із головної таблиці. Хоча, як правило, зовнішній ключ вказує на первинний ключ із пов'язаної головної таблиці, але це необов'язково має бути неодмінною умовою. Зовнішній ключ також може вказувати на інший стовпець, який має унікальне значення.
Загальний синтаксис встановлення зовнішнього ключа на рівні шпальти:
REFERENCES головна_таблиця (стовпець_головної_таблиці)
Для створення обмеження зовнішнього ключа на рівні стовпця після ключового слова REFERENCES вказується ім'я зв'язаної таблиці та у круглих дужках ім'я зв'язаного стовпця, на який вказуватиме зовнішній ключ. Також зазвичай додаються ключові слова FOREIGN KEY , але їх необов'язково вказувати. Після виразу REFERENCES йде вираз ON DELETE та ON UPDATE.
Загальний синтаксис встановлення зовнішнього ключа на рівні таблиці:
FOREIGN KEY (стобець1, стовпець2, ... стовпецьN) REFERENCES головна_таблиця (стовпець_головної_таблиці1, стовпець_головної_таблиці2, ... стовпець_головної_таблиціN)
Наприклад, визначимо дві таблиці та зв'яжемо їх за допомогою зовнішнього ключа:
CREATE TABLE Customers (Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE) CREATE TABLE Orders (Id INT PRIMARY KEY IDENTITY, CustomerId INT REFERENCES Customers (Id), CreatedAt Date);
Тут визначено таблиці Customers та Orders. Customers є головною і представляє клієнта. Orders є залежною та представляє замовлення, зроблене клієнтом. Ця таблиця через стовпець CustomerId пов'язана з таблицею Customers та її стовпцем Id. Тобто стовпець CustomerId є зовнішнім ключем, який свідчить про стовпець Id з таблиці Customers.
Визначення зовнішнього ключа лише на рівні таблиці виглядало б так:
CREATE TABLE Orders (Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id));
За допомогою оператора CONSTRAINT можна встановити ім'я для обмеження зовнішнього ключа. Зазвичай це ім'я починається з префіксу "FK_":
CREATE TABLE Orders (Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, CONSTRAINT FK_Orders_To_Customers FOREIGN KEY (CustomerId) REFERENCES Customers (Id));
У цьому випадку обмеження зовнішнього ключа CustomerId називається "FK_Orders_To_Customers".
ON DELETE та ON UPDATE
За допомогою виразів ON DELETE та ON UPDATE можна встановити дії, які виконуватимуться відповідно при видаленні та зміні зв'язаного рядка з головної таблиці. І для визначення дії ми можемо використовувати такі опції:
CASCADE : автоматично видаляє або змінює рядки із залежної таблиці при видаленні або зміні зв'язаних рядків у головній таблиці.
NO ACTION : запобігає будь-яким діям у залежній таблиці при видаленні або зміні зв'язаних рядків у головній таблиці. Тобто фактично будь-які дії відсутні.
SET NULL : у разі видалення зв'язаного рядка з головної таблиці встановлює для стовпця зовнішнього ключа значення NULL.
SET DEFAULT : у разі видалення зв'язаного рядка з головної таблиці встановлює для стовпця зовнішнього ключа значення за замовчуванням, яке задається за допомогою атрибути DEFAULT. Якщо для стовпця не встановлено значення за замовчуванням, то як нього застосовується значення NULL.
Каскадне видалення
За замовчуванням, якщо на рядок із головної таблиці за зовнішнім ключем посилається будь-який рядок із залежної таблиці, то ми не зможемо видалити цей рядок із головної таблиці. Спочатку нам необхідно буде видалити всі зв'язані рядки із залежної таблиці. І якщо при видаленні рядка з головної таблиці необхідно, щоб було видалено всі зв'язані рядки із залежної таблиці, то застосовується каскадне видалення, тобто опція CASCADE :
CREATE TABLE Orders (Id INT PRIMARY KEY IDENTITY, 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 IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL);
Встановлення значення за замовчуванням
CREATE TABLE Orders (Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET DEFAULT)F oreign Key (зовнішній ключ) є ключем, який використовується для з'єднання двох таблиць. Його іноді також називають ключем, що посилається.
Зовнішній ключ – це стовпець або поєднання стовпців, значення якого відповідають первинному ключі в іншій таблиці.
Відносини між 2 таблицею відповідає первинного ключа в одній із таблиць із зовнішнім ключем у другій таблиці.
Якщо таблиця має первинний ключ, визначений будь-якому полі (їй), ви можете мати дві записи, мають однакове значення цього поля (ей).
Приклад
Розглянемо структуру наступних двох таблиць.
Таблиця 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));
Таблиця ORDERS
CREATE TABLE ORDERS (ID INT NOT NULL, DATE DATETIME, CUSTOMER_ID INT references CUSTOMERS(ID), AMOUNT double, PRIMARY KEY (ID));
Якщо таблиця ORDERS вже створена і зовнішній ключ ще не було встановлено, синтаксис використовується для завдання зовнішнього ключа шляхом зміни таблиці.
ALTER TABLE ORDERS ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);
Видалення обмеження зовнішнього ключа
Щоб видалити обмеження зовнішнього ключа, використовуйте наступний синтаксис SQL.
ALTER TABLE ORDERS DROP FOREIGN KEY;
Операційні системи (ОС)
