Первинний ключ і зовнішній ключ SQLite з прикладами

У розділах нижче пояснюється SQLite обмеження детально, починаючи з PRIMARY KEY та FOREIGN KEY, які визначають та з'єднують таблиці, і охоплюючи правила NOT NULL, DEFAULT, UNIQUE та CHECK, які перевіряють дані в кожному стовпці.

SQLite Обмеження

Обмеження стовпців застосовують правила до значень, вставлених у стовпець, щоб перевірити дані. Ці обмеження визначаються під час створення таблиці, всередині визначення стовпця. Вони забезпечують узгодженість та точність збережених даних, відхиляючи значення, які порушують встановлені вами правила, такі як дублікати, значення NULL або значення, яких немає у пов'язаній таблиці.

SQLite Первинний ключ

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

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

Синтаксис:

Існує кілька різних способів визначення первинного ключа в таблиці:

У самому визначенні стовпця:

ColumnName INTEGER NOT NULL PRIMARY KEY;

Як окреме визначення:

PRIMARY KEY(ColumnName);

Щоб створити комбінацію стовпців як первинний ключ:

PRIMARY KEY(ColumnName1, ColumnName2);

SQLite Обмеження NOT NULL, DEFAULT, UNIQUE та CHECK

Окрім первинного ключа, SQLite надає кілька обмежень стовпців, які перевіряють значення, введені в таблицю. Обмеження NOT NULL, DEFAULT, UNIQUE та CHECK визначені у визначенні стовпця, і кожне з них застосовує певне правило до стовпця. тип даних та цінності.

Обмеження NOT NULL

Команда SQLite Обмеження NOT NULL запобігає тому, щоб стовпець мав значення null:

ColumnName INTEGER  NOT NULL;

Обмеження DEFAULT

З SQLite Обмеження DEFAULT: якщо ви не вставляєте жодного значення в стовпець, замість нього вставляється значення за замовчуванням.

Наприклад:

ColumnName INTEGER DEFAULT 0;

Якщо ви напишете оператор вставки і не вкажете жодного значення для цього стовпця, стовпець матиме значення 0.

УНІКАЛЬНЕ обмеження

Команда SQLite Обмеження UNIQUE запобігає дублюванню значень серед усіх значень стовпця.

Наприклад:

EmployeeId INTEGER NOT NULL UNIQUE;

Це забезпечує унікальність значення «EmployeeId»; дублювання значень заборонено. Зверніть увагу, що це стосується лише значень стовпця «EmployeeId».

ПЕРЕВІРИТИ обмеження

Команда SQLite Обмеження CHECK встановлює умову для перевірки вставленого значення. Якщо значення не відповідає умові, воно не буде вставлено.

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

У стовпці «Кількість» не можна вставити значення менше 10.

SQLite Зовнішній ключ

Команда SQLite Зовнішній ключ — це обмеження, яке перевіряє існування значення, присутнього в одній таблиці, в іншій таблиці, яка має зв'язок з першою таблицею, де визначено зовнішній ключ.

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

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

Зверніть увагу, що обмеження зовнішнього ключа не ввімкнені за замовчуванням у SQLiteСпочатку їх потрібно ввімкнути, виконавши таку команду:

PRAGMA foreign_keys = ON;

Обмеження зовнішнього ключа були введені в SQLite починаючи з версії 3.6.19.

Приклад SQLite Зовнішній ключ

Припустимо, у нас є дві таблиці: Студенти та Відділи.

Таблиця «Студенти» містить список студентів, а таблиця «Відділи» містить список відділів. Кожен студент належить до відділу, тобто кожен студент має стовпець departmentId.

Тепер ми побачимо, як обмеження зовнішнього ключа може бути корисним для забезпечення того, щоб значення ідентифікатора відділу з таблиці Students обов'язково існувало в таблиці Departments.

Отже, якщо ми створимо обмеження зовнішнього ключа для DepartmentId у таблиці Students, кожен вставлений departmentId має бути присутнім у таблиці Departments.

CREATE TABLE [Departments] (
	[DepartmentId] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
	[DepartmentName] NVARCHAR(50)  NULL
);
CREATE TABLE [Students] (
	[StudentId] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
	[StudentName] NVARCHAR(50)  NULL,
	[DepartmentId] INTEGER  NOT NULL,
	[DateOfBirth] DATE  NULL,
	FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId)
);

Щоб перевірити, як обмеження зовнішнього ключа можуть запобігти вставці невизначеного елемента або значення в таблицю, яка має зв'язок з іншою таблицею, ми розглянемо наступний приклад.

У цьому прикладі таблиця Departments має зв'язок зовнішнього ключа з таблицею Students, тому будь-яке значення departmentId, вставлене в таблицю Students, має існувати в таблиці Departments. Якщо ви спробуєте вставити значення departmentId, якого немає в таблиці Departments, обмеження зовнішнього ключа не дозволить вам це зробити.

Давайте вставимо два відділи, «ІТ» та «Мистецтво», у таблицю «Відділи» з наступним ВСТАВЛЕННЯ запитів:

INSERT INTO Departments VALUES(1, 'IT');
INSERT INTO Departments VALUES(2, 'Arts');

Ці два оператори повинні вставити два відділи до таблиці «Відділи». Ви можете підтвердити, що ці два значення були вставлені, виконавши після цього запит «SELECT * FROM Відділи»:

Потім спробуйте вставити нового студента з ідентифікатором відділу (departmentId), якого немає в таблиці Departments:

INSERT INTO Students(StudentName,DepartmentId) VALUES('John', 5);

Рядок не буде вставлено, і ви отримаєте помилку: обмеження ЗОВНІШНЬОГО КЛЮЧА не вдалося.

Різниця між первинним ключем та зовнішнім ключем у SQLite

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

У прикладі зі студентами та відділами, DepartmentId є первинним ключем таблиці Departments та зовнішнім ключем у таблиці Students, який пов'язує кожного студента з дійсним відділом.

SQLite Складений первинний ключ

Складений первинний ключ — це первинний ключ, що складається з двох або більше стовпців. Він використовується, коли жоден стовпець не є унікальним сам по собі, але комбінація стовпців унікальна для кожного рядка. SQLite трактує об'єднані значення як один ключ.

Наприклад, таблиця зарахування може дозволяти одному й тому ж студенту відвідувати багато курсів та один і той самий курс для багатьох студентів, проте кожна пара студент-курс повинна з'являтися лише один раз:

CREATE TABLE Enrollments (
	StudentId INTEGER NOT NULL,
	CourseId INTEGER NOT NULL,
	Grade TEXT,
	PRIMARY KEY (StudentId, CourseId)
);

Тут ні StudentId, ні CourseId не є унікальними окремо, але пара (StudentId, CourseId) є унікальною, тому один і той самий студент не може бути зарахований на один і той самий курс двічі. Зверніть увагу на наступне, коли ви використовуєте складений ключ:

• Використовуйте складений ключ, коли один стовпець не може однозначно ідентифікувати рядок.
• Кожен стовпець у складеному ключі відповідає правилам первинного ключа, тому об'єднане значення має бути унікальним і не дорівнювати null.
• Складений ключ записується як окремий пункт PRIMARY KEY на рівні таблиці, а не всередині визначення одного стовпця.

SQLite Дії зовнішнього ключа: ПРИ ВИДАЛЕННІ та ПРИ ОНОВЛЕННІ

Зовнішній ключ також може контролювати, що відбувається з дочірніми рядками, коли батьківський рядок, на який вони посилаються, видаляється або оновлюється. Ці дії посилання додаються за допомогою речень ON DELETE та ON UPDATE під час визначення зовнішнього ключа. SQLite підтримує п'ять дій:

• НІЯКИХ ДІЙ — дія за замовчуванням, яка викликає помилку, якщо дочірні рядки все ще посилаються на батьківський.
• ОБМЕЖЕННЯ — запобігає негайному видаленню або оновленню, до виконання будь-яких інших змін.
• ВИСТАВИТИ НУЛЬ — встановлює дочірній стовпець зовнішнього ключа на null.
• ВСТАНОВИТИ ЗА ЗАМОВЧУВАННЯМ — встановлює для дочірнього стовпця зовнішнього ключа оголошене значення за замовчуванням.
• CASCADE — застосовує ту саму зміну до дочірніх рядків, тому видалення батьківського елемента видаляє його дочірні елементи.

У наведеному нижче прикладі таблиця "Студенти" відтворюється таким чином, що видалення відділу автоматично видаляє його студентів, а оновлення ідентифікатора відділу оновлює список відповідних студентів:

CREATE TABLE Students (
	StudentId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	StudentName NVARCHAR(50) NULL,
	DepartmentId INTEGER NOT NULL,
	FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

Пам'ятайте, що дії з посиланнями виконуються лише тоді, коли ввімкнено підтримку зовнішніх ключів, тому запускайте PRAGMA foreign_keys = ON на початку кожного з'єднання. Без нього, SQLite аналізує речення ON DELETE та ON UPDATE, але не застосовує їх примусово.

Поширені запитання