Ограничения и целостность данных
Правила, которые база данных соблюдает сама, чтобы плохие данные никогда не попали внутрь
Главная идея: Ограничение (constraint) - это правило, прикреплённое к таблице, которому должна удовлетворять каждая строка. База проверяет его при каждом
INSERT и UPDATE и отклоняет всё, что его нарушает. Вместо надежды на безупречность кода приложения вы переносите гарантию в саму схему, где она действует для всех клиентов, скриптов и ручных правок.
К концу вы сможете:
- запрещать пропущенные значения через
NOT NULL, - запрещать дубликаты через
UNIQUE, - задавать бизнес-правила через
CHECK, - подставлять разумные значения по умолчанию через
DEFAULT, - синхронизировать таблицы через
FOREIGN KEYи его действияON DELETE/ON UPDATE.
1. Наша учебная схема: небольшой магазин
Каждый раздел ниже обращается к одной и той же крошечной базе интернет-магазина. Таблица categories группирует товары, products хранит товары, customers - покупателей, а orders фиксирует покупки. Создадим их один раз:
CREATE TABLE categories (
category_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(8,2) NOT NULL CHECK (price > 0),
stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
category_id INT,
CONSTRAINT fk_cat FOREIGN KEY (category_id)
REFERENCES categories(category_id)
ON DELETE SET NULL ON UPDATE CASCADE
);
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
email VARCHAR(120) NOT NULL UNIQUE,
country CHAR(2) NOT NULL DEFAULT 'US'
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
total DECIMAL(10,2) NOT NULL DEFAULT 0,
CONSTRAINT fk_cust FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE ON UPDATE CASCADE
);Уже этот один блок CREATE TABLE использует все типы ограничений, которые мы изучим: PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT и FOREIGN KEY. Разберём их по одному.