Оволодіння обмеженнями SQL: Охоронці цілісності даних - javascript.org.ua

Правила, що зберігають вашу базу даних чистою та консистентною

Що таке обмеження SQL?

Обмеження (Constraints) — це правила, які накладаються на стовпці бази даних для підтримки цілісності та точності даних.
Вони діють як охоронці, забезпечуючи, щоб лише дійсні дані потрапляли до ваших таблиць.

Давайте розглянемо основні обмеження з прикладами:

PRIMARY KEY — Унікальний Ідентифікатор:

-- Одноколонковий первинний ключ  
CREATE TABLE students (  
 student_id INT PRIMARY KEY,  
 name VARCHAR(50)  
);  

-- Складений первинний ключ (декілька колонок)  
CREATE TABLE enrollments (  
 student_id INT,  
 course_id INT,  
 PRIMARY KEY (student_id, course_id)  
);

Основні характеристики:

Повинен містити УНІКАЛЬНІ значення
Не може містити значення NULL
Тільки один PRIMARY KEY (первинний ключ) на таблицю
Може бути складеним (з кількох колонок)
Автоматично індексується базою даних

FOREIGN KEY — Будівельник Відносин:

-- Батьківська таблиця (Посилана таблиця)  
CREATE TABLE departments (  
 dept_id INT PRIMARY KEY,  
 dept_name VARCHAR(50)  
);  

-- Дитяча таблиця (Таблиця, що посилається)  
CREATE TABLE employees (  
 emp_id INT PRIMARY KEY,  
 dept_id INT,  
 FOREIGN KEY (dept_id) REFERENCES departments(dept_id)  
);  

-- Зовнішній ключ з діями ON DELETE та ON UPDATE  
CREATE TABLE orders (  
 order_id INT PRIMARY KEY,  
 customer_id INT,  
 FOREIGN KEY (customer_id)   
 REFERENCES customers(customer_id)  
 ON DELETE CASCADE -- Автоматично видаляє замовлення, коли видаляється клієнт  
 ON UPDATE CASCADE -- Оновлює customer_id, коли змінюється id клієнта  
);  

-- Різні дії зовнішнього ключа  
CREATE TABLE order_items (  
 item_id INT PRIMARY KEY,  
 order_id INT,  
 FOREIGN KEY (order_id)   
 REFERENCES orders(order_id)  
 ON DELETE SET NULL -- Встановлює order_id в NULL, коли замовлення видалено  
 ON UPDATE NO ACTION -- Запобігає оновленню посилання на order_id  
);  

-- Складений зовнішній ключ  
CREATE TABLE project_assignments (  
 project_id INT,  
 dept_id INT,  
 employee_id INT,  
 FOREIGN KEY (project_id, dept_id)   
 REFERENCES projects(project_id, dept_id)  
 ON DELETE RESTRICT -- Запобігає видаленню проекту, якщо існують призначення  
);

Пояснення Дій Зовнішнього Ключа:

Дії ON DELETE:

CASCADE: Автоматично видаляє дочірні записи
SET NULL: Встановлює зовнішній ключ у NULL
SET DEFAULT: Встановлює зовнішній ключ у значення за замовчуванням
RESTRICT: Запобігає видаленню, якщо існують дочірні записи
NO ACTION: Подібно до RESTRICT

Дії ON UPDATE:

CASCADE: Оновлює дочірні записи, щоб вони відповідали новому значенню батьківського ключа
SET NULL: Встановлює зовнішній ключ у NULL
SET DEFAULT: Встановлює зовнішній ключ у значення за замовчуванням
RESTRICT: Запобігає оновленню, якщо існують дочірні записи
NO ACTION: Подібно до RESTRICT

UNIQUE — Запобіжник Дублікатів:

CREATE TABLE users (  
 id INT PRIMARY KEY,  
 email VARCHAR(100) UNIQUE, -- Без дублікатів email  
 phone VARCHAR(15) UNIQUE, -- Без дублікатів телефонів  
 username VARCHAR(50) UNIQUE -- Без дублікатів імен користувачів  
);  

-- Унікальне обмеження на кілька колонок  
CREATE TABLE order_items (  
 order_id INT,  
 product_id INT,  
 quantity INT,  
 UNIQUE (order_id, product_id)  
);

Основні моменти:

Дозволяє значення NULL (на відміну від PRIMARY KEY)
Може бути кілька обмежень UNIQUE на таблицю
Може бути одиночним або складеним з кількох колонок
Допомагає підтримувати унікальність даних

NOT NULL — Захисник Пустих Значень:

CREATE TABLE orders (  
 order_id INT PRIMARY KEY,  
 customer_name VARCHAR(50) NOT NULL,  
 order_date DATE NOT NULL,  
 total_amount DECIMAL(10,2) NOT NULL,  
 shipping_address VARCHAR(200) NOT NULL,  
 status VARCHAR(20) DEFAULT 'Pending' NOT NULL  
);

Типові випадки використання:

Обов'язкові поля форми
Критичні бізнес-дані
Обов'язкові зв'язки
Обов'язкові часові мітки
Поля статусу

CHECK — Застосування Бізнес-Правил:

CREATE TABLE products (  
 id INT PRIMARY KEY,  
 price DECIMAL(10,2) CHECK (price > 0),  
 stock INT CHECK (stock >= 0),  
 category VARCHAR(20) CHECK (category IN ('Electronics', 'Clothing', 'Books')),  
 discount_price DECIMAL(10,2),  
 manufacture_date DATE,  
 expiry_date DATE,  
 CONSTRAINT valid_discount CHECK (discount_price <= price),  
 CONSTRAINT valid_dates CHECK (expiry_date > manufacture_date)  
);

Випадки використання:
- Перевірка діапазону значень
- Застосування бізнес-правил
- Обмеження типів даних
- Складні умовні перевірки
- Перехід статусів

Реалізація кількох обмежень:

CREATE TABLE employees (  
 emp_id INT PRIMARY KEY,  
 email VARCHAR(100) UNIQUE NOT NULL,  
 salary DECIMAL(10,2) NOT NULL CHECK (salary > 0),  
 dept_id INT NOT NULL,  
 hire_date DATE NOT NULL,  
 FOREIGN KEY (dept_id) REFERENCES departments(dept_id)  
 ON DELETE RESTRICT  
 ON UPDATE CASCADE,  
 CONSTRAINT valid_hire_date CHECK (hire_date <= CURRENT_DATE)  
);

Пам'ятайте: добре реалізовані обмеження є необхідними для:

Запобігання пошкодженню даних
Підтримки консистентності даних
Забезпечення референтної цілісності
Застосування бізнес-логіки
Побудови надійних додатків
Автоматизації перевірки даних
Зниження потреби в перевірках на рівні додатка

Кращі практики:

Давайте обмеженням осмислені назви
Плануйте обмеження на етапі проектування бази даних
Ураховуйте вплив на продуктивність
Документуйте призначення обмежень
Тестуйте поведінку обмежень
Регулярно переглядайте обмеження
Балансуйте гнучкість і контроль
Завжди індексуйте колонки зовнішнього ключа
Ураховуйте вплив дій ON DELETE та ON UPDATE
Плануйте масштабованість при проектуванні зв'язків

Висновок:

Обмеження бази даних є охоронцями цілісності ваших даних, забезпечуючи їх якість і надійність через різні типи — PRIMARY KEY (первинний ключ), FOREIGN KEY (зовнішній ключ), UNIQUE (унікальне обмеження), NOT NULL (не NULL) та CHECK (перевірка).
Кожен тип обмеження має свою конкретну мету в підтримці консистентності даних та застосуванні бізнес-правил.

Перекладено з: Mastering SQL Constraints: The Guardians of Data Integrity