Пошук уроків, статей та іншого контенту
Зв’яжіть таблиці зовнішнім ключем і забезпечте посилальну цілісність між записами.
Зовнішній ключ (FOREIGN KEY) — це обмеження, яке пов’язує стовпці однієї таблиці з первинним або унікальним ключем іншої таблиці.
Наприклад:
таблиця customers зберігає клієнтів;
таблиця orders зберігає замовлення;
кожне замовлення має належати певному клієнту.
Зовнішній ключ гарантує, що в orders.customer_id не можна записати ідентифікатор клієнта, якого немає в customers.
Це називається посилальною цілісністю.
Зовнішній ключ можна оголосити під час створення таблиці:
CREATE TABLE customers (
customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name text NOT NULL
);
CREATE TABLE orders (
order_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL,
order_date date NOT NULL DEFAULT CURRENT_DATE,
CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
);У цьому прикладі:
customers.customer_id — батьківський ключ;
orders.customer_id — зовнішній ключ;
кожне значення orders.customer_id повинно відповідати існуючому customers.customer_id;
NOT NULL додатково вимагає, щоб замовлення завжди мало клієнта.
За замовчуванням зовнішній ключ посилається на PRIMARY KEY або UNIQUE-стовпець батьківської таблиці.
Спочатку потрібно додати запис у батьківську таблицю:
INSERT INTO customers (full_name)
VALUES ('Олена Коваль');
INSERT INTO orders (customer_id)
VALUES (1);Якщо клієнта з ідентифікатором 1 не існує, вставка замовлення завершиться помилкою:
INSERT INTO orders (customer_id)
VALUES (999);PostgreSQL повідомить, що значення зовнішнього ключа не має відповідного запису в таблиці customers.
Повний приклад:
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name text NOT NULL
);
CREATE TABLE orders (
order_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL,
order_date date NOT NULL DEFAULT CURRENT_DATE,
CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
);
INSERT INTO customers (full_name)
VALUES
('Олена Коваль'),
('Андрій Мельник');
INSERT INTO orders (customer_id)
VALUES
(1),
(1),
(2);
SELECT
orders.order_id,
customers.full_name,
orders.order_date
FROM orders
JOIN customers
ON customers.customer_id = orders.customer_id
ORDER BY orders.order_id;Якщо для батьківського запису існують дочірні записи, PostgreSQL за замовчуванням не дозволить його видалити.
DELETE FROM customers
WHERE customer_id = 1;Ця операція завершиться помилкою, якщо клієнт має замовлення. Така поведінка захищає від появи замовлень, які посилаються на неіснуючого клієнта.
Поведінку можна змінити за допомогою ON DELETE.
ON DELETE RESTRICTЗабороняє видаляти батьківський запис, якщо на нього посилаються дочірні записи.
CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
ON DELETE RESTRICTRESTRICT є типовою поведінкою. Його часто використовують для важливих історичних даних, наприклад замовлень.
ON DELETE CASCADEАвтоматично видаляє дочірні записи разом із батьківським:
CREATE TABLE order_items (
order_item_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id integer NOT NULL,
product_name text NOT NULL,
CONSTRAINT order_items_order_fk
FOREIGN KEY (order_id)
REFERENCES orders (order_id)
ON DELETE CASCADE
);Якщо видалити замовлення, усі його позиції також будуть видалені:
DELETE FROM orders
WHERE order_id = 1;CASCADE зручний для технічних дочірніх даних, які не мають сенсу без батьківського запису. Водночас його потрібно використовувати обережно: одне видалення може видалити багато рядків у пов’язаних таблицях.
ON DELETE SET NULLВстановлює NULL у дочірньому стовпці:
CREATE TABLE support_tickets (
ticket_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
assigned_customer_id integer,
CONSTRAINT tickets_customer_fk
FOREIGN KEY (assigned_customer_id)
REFERENCES customers (customer_id)
ON DELETE SET NULL
);Для цього дочірній стовпець не повинен мати обмеження NOT NULL.
Такий варіант підходить, коли дочірній запис потрібно зберегти, але зв’язок із видаленим батьківським записом більше не актуальний.
ON DELETE SET DEFAULTВстановлює для зовнішнього ключа значення за замовчуванням:
CREATE TABLE tasks (
task_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
owner_id integer DEFAULT 1,
CONSTRAINT tasks_owner_fk
FOREIGN KEY (owner_id)
REFERENCES customers (customer_id)
ON DELETE SET DEFAULT
);Значення за замовчуванням також повинно відповідати існуючому запису в батьківській таблиці. Інакше операція видалення завершиться помилкою.
За замовчуванням PostgreSQL не дозволяє змінити значення ключа в батьківській таблиці, якщо на нього посилаються дочірні записи.
Для автоматичного оновлення дочірніх значень використовують ON UPDATE CASCADE:
CREATE TABLE payments (
payment_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL,
CONSTRAINT payments_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
ON UPDATE CASCADE
);Якщо customers.customer_id зміниться, відповідні значення payments.customer_id також зміняться.
На практиці первинні ключі зазвичай не змінюють, тому ON UPDATE CASCADE потрібен рідше, ніж ON DELETE CASCADE.
Якщо таблиці вже створені, обмеження можна додати через ALTER TABLE:
ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id);Перед виконанням цієї команди всі наявні значення orders.customer_id повинні бути коректними. Якщо хоча б одне значення не має відповідного клієнта, PostgreSQL не створить обмеження.
Спочатку можна знайти некоректні посилання:
SELECT orders.*
FROM orders
LEFT JOIN customers
ON customers.customer_id = orders.customer_id
WHERE customers.customer_id IS NULL;За замовчуванням PostgreSQL перевіряє наявні рядки під час створення зовнішнього ключа. Для великих таблиць можна спочатку створити обмеження як NOT VALID:
ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
NOT VALID;Таке обмеження перевіряє нові та змінені рядки, але не перевіряє одразу старі дані.
Після виправлення старих некоректних записів обмеження можна перевірити повністю:
ALTER TABLE orders
VALIDATE CONSTRAINT orders_customer_fk;NOT VALID варто використовувати свідомо: до виконання VALIDATE CONSTRAINT у таблиці можуть залишатися старі порушення посилальної цілісності.
Зовнішній ключ може складатися з кількох стовпців. У такому разі батьківська таблиця повинна мати первинний або унікальний ключ із відповідною комбінацією стовпців:
CREATE TABLE warehouse_products (
warehouse_id integer NOT NULL,
product_id integer NOT NULL,
quantity integer NOT NULL DEFAULT 0,
PRIMARY KEY (warehouse_id, product_id)
);
CREATE TABLE product_reservations (
reservation_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
warehouse_id integer NOT NULL,
product_id integer NOT NULL,
quantity integer NOT NULL,
CONSTRAINT reservations_product_fk
FOREIGN KEY (warehouse_id, product_id)
REFERENCES warehouse_products (warehouse_id, product_id)
);Тут посилання перевіряється для всієї пари (warehouse_id, product_id). Наявність окремо такого складу і такого товару ще не гарантує, що конкретна пара існує.
PostgreSQL автоматично створює індекс для PRIMARY KEY та UNIQUE, на які посилається зовнішній ключ. Але індекс на дочірньому стовпці зовнішнього ключа автоматично не створюється.
Для частих пошуків і видалень батьківських записів дочірній стовпець часто варто індексувати:
CREATE INDEX orders_customer_id_idx
ON orders (customer_id);Індекс особливо корисний, коли:
у дочірній таблиці багато рядків;
часто виконуються запити з фільтрацією за зовнішнім ключем;
використовується ON DELETE CASCADE;
перевіряється наявність пов’язаних записів.
Не можна спочатку вставити дочірній запис, якщо відповідного батьківського запису ще немає:
-- Спочатку потрібно створити клієнта.
INSERT INTO customers (full_name)
VALUES ('Марія Бондар');
-- Потім можна створити замовлення для цього клієнта.
INSERT INTO orders (customer_id)
VALUES (3);NULLЯкщо стовпець не має NOT NULL, значення NULL дозволене:
CREATE TABLE comments (
comment_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer,
CONSTRAINT comments_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
);Такий коментар може не мати клієнта. Якщо це неприпустимо для предметної області, потрібно явно додати NOT NULL.
CASCADE без оцінки наслідківON DELETE CASCADE може видалити цілий ланцюжок пов’язаних даних. Перед його використанням потрібно визначити, чи справді дочірні записи не мають зберігатися окремо.
Стовпці зовнішнього та батьківського ключа повинні бути сумісними за типом. Наприклад, для integer-ідентифікатора слід використовувати сумісний integer, а не довільний текстовий стовпець.
Зовнішній ключ не може посилатися на звичайний стовпець, у якому можуть повторюватися значення. Цільовий стовпець повинен бути PRIMARY KEY або мати відповідне обмеження UNIQUE.
FOREIGN KEY зв’язує дочірню таблицю з батьківською.
Він не дозволяє створювати посилання на неіснуючі записи.
Цільовий стовпець повинен бути первинним або унікальним ключем.
NOT NULL визначає, чи може дочірній запис існувати без посилання.
ON DELETE задає поведінку під час видалення батьківського запису.
ON UPDATE задає поведінку під час зміни батьківського ключа.
CASCADE, SET NULL і SET DEFAULT потрібно вибирати відповідно до правил предметної області.
Індекс на дочірньому стовпці зовнішнього ключа за потреби створюють окремо.