Пошук уроків, статей та іншого контенту
Зв’яжіть таблиці зовнішніми ключами й налаштуйте поведінку ON DELETE та ON UPDATE.
Зовнішній ключ (FOREIGN KEY) — це обмеження, яке встановлює зв’язок між таблицями.
Він гарантує, що значення в дочірній таблиці посилається на наявний рядок у батьківській таблиці.
Наприклад:
customers — батьківська таблиця;
orders — дочірня таблиця;
orders.customer_id посилається на customers.id.
CREATE TABLE customers (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers (id)
);Тепер PostgreSQL не дозволить створити замовлення для неіснуючого клієнта:
INSERT INTO orders (customer_id)
VALUES (999);Якщо клієнта з id = 999 немає, PostgreSQL поверне помилку порушення зовнішнього ключа.
Спочатку потрібно додати клієнта:
INSERT INTO customers (name)
VALUES ('Олена');
INSERT INTO orders (customer_id)
VALUES (1);Референційна цілісність означає, що зв’язки між таблицями залишаються коректними:
дочірній рядок не може посилатися на неіснуючий батьківський рядок;
видалення батьківського рядка не повинно залишити некоректних посилань;
оновлення значення, на яке посилаються дочірні рядки, має бути контрольованим.
Зовнішній ключ перевіряється під час:
INSERT або UPDATE у дочірній таблиці;
UPDATE ключового стовпця в батьківській таблиці;
DELETE або UPDATE батьківського рядка.
У зовнішньому ключі:
батьківський ключ — це стовпець у таблиці, на який посилаються;
дочірній ключ — це стовпець у таблиці, що містить посилання.
FOREIGN KEY (customer_id)
REFERENCES customers (id)У цьому прикладі:
customers.id — батьківський ключ;
orders.customer_id — дочірній ключ.
Стовпець у батьківській таблиці повинен бути первинним ключем або мати обмеження UNIQUE.
CREATE TABLE departments (
id integer PRIMARY KEY,
code text UNIQUE NOT NULL
);
CREATE TABLE employees (
id integer PRIMARY KEY,
department_code text NOT NULL,
FOREIGN KEY (department_code)
REFERENCES departments (code)
);Параметр ON DELETE визначає, що робити з дочірніми рядками, коли видаляється батьківський рядок.
Основні варіанти:
NO ACTION — стандартна поведінка;
RESTRICT — заборонити видалення;
CASCADE — видалити дочірні рядки;
SET NULL — встановити NULL;
SET DEFAULT — встановити значення за замовчуванням.
ON DELETE NO ACTIONЦе поведінка за замовчуванням:
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL
REFERENCES customers (id)
ON DELETE NO ACTION
);Якщо для клієнта існують замовлення, його видалення завершиться помилкою.
NO ACTION перевіряє цілісність після виконання операції. Це має значення для відкладених обмежень, але в більшості звичайних випадків результат такий самий, як у RESTRICT.
ON DELETE RESTRICTRESTRICT явно забороняє видаляти батьківський рядок, якщо на нього посилаються дочірні рядки:
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL,
CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers (id)
ON DELETE RESTRICT
);Це доречно, коли дочірні дані не можна видаляти автоматично. Наприклад, замовлення може бути частиною історії продажів.
ON DELETE CASCADECASCADE автоматично видаляє дочірні рядки:
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL,
FOREIGN KEY (customer_id)
REFERENCES customers (id)
ON DELETE CASCADE
);Якщо видалити клієнта, PostgreSQL також видалить усі його замовлення.
Цей варіант підходить для даних, які не мають сенсу без батьківського рядка. Наприклад:
рядки замовлення без самого замовлення;
вкладені налаштування користувача;
тимчасові дочірні записи.
CASCADE потрібно використовувати обережно: одна операція DELETE може видалити великий ланцюжок пов’язаних даних.
ON DELETE SET NULLSET NULL залишає дочірній рядок, але очищає його посилання:
CREATE TABLE support_tickets (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
assigned_customer_id integer,
FOREIGN KEY (assigned_customer_id)
REFERENCES customers (id)
ON DELETE SET NULL
);Цей варіант можливий лише тоді, коли дочірній стовпець дозволяє NULL. Тому assigned_customer_id не має NOT NULL.
Після видалення клієнта звернення залишиться, але більше не буде призначене цьому клієнту.
ON DELETE SET DEFAULTSET DEFAULT встановлює дочірньому стовпцю значення за замовчуванням:
CREATE TABLE categories (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE products (
id integer PRIMARY KEY,
category_id integer NOT NULL DEFAULT 1,
FOREIGN KEY (category_id)
REFERENCES categories (id)
ON DELETE SET DEFAULT
);Значення за замовчуванням також має посилатися на наявний рядок. Наприклад, категорія з id = 1 повинна існувати до видалення інших категорій.
Параметр ON UPDATE визначає, що робити з дочірніми рядками, коли змінюється значення батьківського ключа.
Типовий варіант:
CREATE TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
id integer PRIMARY KEY,
customer_id integer NOT NULL,
FOREIGN KEY (customer_id)
REFERENCES customers (id)
ON UPDATE CASCADE
);Якщо виконати:
UPDATE customers
SET id = 10
WHERE id = 1;то orders.customer_id автоматично зміниться з 1 на 10.
За замовчуванням використовується ON UPDATE NO ACTION. У такому разі зміна батьківського ключа буде заборонена, якщо існують дочірні посилання.
Первинні ключі зазвичай не змінюють, тому ON UPDATE CASCADE потрібен рідше, ніж ON DELETE CASCADE. Проте він може бути корисним для природних ключів, наприклад коду або зовнішнього ідентифікатора.
Наступний приклад створює клієнтів, замовлення та рядки замовлень:
клієнта не можна видалити, якщо в нього є замовлення;
при видаленні замовлення його рядки видаляються автоматично;
при зміні ідентифікатора клієнта посилання в замовленнях оновлюються.
DROP SCHEMA IF EXISTS shop CASCADE;
CREATE SCHEMA shop;
SET search_path TO shop;
CREATE TABLE customers (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers (id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);
CREATE TABLE order_items (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id integer NOT NULL,
product_name text NOT NULL,
quantity integer NOT NULL CHECK (quantity > 0),
CONSTRAINT order_items_order_fk
FOREIGN KEY (order_id)
REFERENCES orders (id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
INSERT INTO customers (name)
VALUES ('Олена'), ('Андрій');
INSERT INTO orders (customer_id)
VALUES (1);
INSERT INTO order_items (order_id, product_name, quantity)
VALUES
(1, 'Клавіатура', 1),
(1, 'Миша', 2);
-- Зміна первинного ключа клієнта оновить orders.customer_id.
UPDATE customers
SET id = 10
WHERE id = 1;
SELECT *
FROM orders;
-- Видалення замовлення також видалить його рядки.
DELETE FROM orders
WHERE id = 1;
SELECT *
FROM order_items;
-- Тепер клієнта можна видалити, оскільки замовлень у нього більше немає.
DELETE FROM customers
WHERE id = 10;Якби перед видаленням замовлення виконати:
DELETE FROM customers
WHERE id = 10;PostgreSQL відхилив би операцію через ON DELETE RESTRICT.
Зовнішній ключ можна додати після створення таблиць:
ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers (id)
ON DELETE RESTRICT
ON UPDATE CASCADE;Перед додаванням обмеження всі наявні значення orders.customer_id повинні відповідати рядкам у customers.id.
Якщо в даних уже є помилкові посилання, спочатку потрібно:
виправити значення;
створити відповідні батьківські рядки;
або видалити некоректні дочірні рядки.
Зовнішній ключ може складатися з кількох стовпців. Тоді батьківська таблиця повинна мати відповідний складений первинний або унікальний ключ.
CREATE TABLE warehouse_stock (
warehouse_id integer NOT NULL,
product_id integer NOT NULL,
amount integer NOT NULL CHECK (amount >= 0),
PRIMARY KEY (warehouse_id, product_id)
);
CREATE TABLE reservations (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
warehouse_id integer NOT NULL,
product_id integer NOT NULL,
amount integer NOT NULL CHECK (amount > 0),
CONSTRAINT reservations_stock_fk
FOREIGN KEY (warehouse_id, product_id)
REFERENCES warehouse_stock (warehouse_id, product_id)
);Тут пара (warehouse_id, product_id) у reservations повинна існувати в warehouse_stock.
PostgreSQL автоматично створює індекс для первинного ключа або UNIQUE у батьківській таблиці. Але індекс для дочірнього стовпця зовнішнього ключа автоматично не створюється.
Для великих таблиць дочірній стовпець часто варто індексувати:
CREATE INDEX orders_customer_id_idx
ON orders (customer_id);Такий індекс може пришвидшити:
пошук усіх дочірніх рядків батьківського запису;
перевірки під час DELETE або UPDATE батьківського рядка;
об’єднання таблиць через JOIN.
Це не є вимогою для коректності зовнішнього ключа, але важливо для продуктивності.
Зазвичай зовнішній ключ перевіряється одразу після кожної операції. За потреби перевірку можна відкласти до завершення транзакції:
CREATE TABLE parent_records (
id integer PRIMARY KEY
);
CREATE TABLE child_records (
id integer PRIMARY KEY,
parent_id integer NOT NULL,
CONSTRAINT child_parent_fk
FOREIGN KEY (parent_id)
REFERENCES parent_records (id)
DEFERRABLE INITIALLY DEFERRED
);У такому випадку PostgreSQL перевірить обмеження під час COMMIT, а не відразу після INSERT або UPDATE.
Відкладені обмеження корисні для складних пакетних змін, але для більшості звичайних зв’язків достатньо стандартної негайної перевірки.
CASCADE без оцінки наслідківON DELETE CASCADE може видалити не лише один дочірній рядок, а ціле дерево пов’язаних даних.
Перед використанням потрібно визначити, чи справді дочірні записи не мають цінності без батьківського.
SET NULL разом із NOT NULLТаке визначення суперечить саме собі:
CREATE TABLE orders (
id integer PRIMARY KEY,
customer_id integer NOT NULL
REFERENCES customers (id)
ON DELETE SET NULL
);Під час видалення клієнта PostgreSQL не зможе встановити NULL у стовпець, якому це заборонено.
Перед вставленням дочірнього рядка батьківський рядок уже має існувати:
INSERT INTO orders (customer_id)
VALUES (5);Ця операція завершиться помилкою, якщо клієнта з id = 5 немає.
За RESTRICT або NO ACTION спочатку потрібно видалити чи змінити дочірні рядки, а вже потім — батьківський рядок:
DELETE FROM order_items
WHERE order_id = 1;
DELETE FROM orders
WHERE id = 1;
DELETE FROM customers
WHERE id = 10;Стовпці зовнішнього та батьківського ключів повинні мати сумісні типи. Наприклад, для integer варто використовувати integer, а для bigint — bigint.
Зовнішній ключ підтримує зв’язок між батьківською та дочірньою таблицями.
Батьківський стовпець повинен бути первинним або унікальним ключем.
ON DELETE RESTRICT і NO ACTION забороняють видалення батьківського рядка з посиланнями.
ON DELETE CASCADE автоматично видаляє дочірні записи.
ON DELETE SET NULL очищає посилання, тому дочірній стовпець має дозволяти NULL.
ON DELETE SET DEFAULT використовує значення за замовчуванням, яке також має бути коректним посиланням.
ON UPDATE CASCADE синхронізує дочірні посилання зі зміною батьківського ключа.
Індекс на дочірньому стовпці зовнішнього ключа PostgreSQL автоматично не створює.