Външни ключове: връзки между таблиците

Накратко: Външният ключ (REFERENCES) пази връзката между две таблици, а ON DELETE казва какво става със свързаните редове при изтриване: CASCADE, SET NULL или RESTRICT.

Проблем

Изтрий ми профила

Мария Иванова пише: „Моля, изтрийте профила ми.“ Опитваш:

SQLбаза: магазин

DELETE FROM customers WHERE id = 1;

DELETE FROM customers WHERE id = 1;

▶ ИзпълниВърни заявкатаНова база

Базата отказва. Мария има 12 поръчки, а всяка от тях сочи към нея. Ако клиентът изчезне, поръчките ще сочат към никого.

Какво трябва да стане с поръчките ѝ? А с отзивите ѝ? А със списъка ѝ с любими продукти? Отговорът е различен за всяка таблица. И се записва в схемата.

Идеята

REFERENCES

Външният ключ вече го познаваш: колона, която сочи към ред в друга таблица. В CREATE TABLE се пише с REFERENCES таблица(колона). Така са направени поръчките на магазина:

orders

• PKidinteger
• FKcustomer_idinteger *→ customers
• ordered_attext *
• statustext *

Ето нова таблица: любимите продукти на всеки клиент. Първият ред влиза, вторият сочи към клиент 99, който го няма:

SQLбаза: магазин

CREATE TABLE wishlist (
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  PRIMARY KEY (customer_id, product_id)
);
INSERT INTO wishlist VALUES (1, 17);
INSERT INTO wishlist VALUES (99, 17);

CREATE TABLE wishlist ( customer_id INTEGER NOT NULL REFERENCES customers(id), product_id INTEGER NOT NULL REFERENCES products(id), PRIMARY KEY (customer_id, product_id) ); INSERT INTO wishlist VALUES (1, 17); INSERT INTO wishlist VALUES (99, 17);

▶ ИзпълниВърни заявкатаНова база

Сега базата сама пази връзките. Ред, който сочи към несъществуващ клиент или продукт, не влиза.

Пробвай

Любимите продукти

Таблицата wishlist вече има колоните си. Направи customer_id да сочи към customers, а product_id към products.

SQLбаза: магазин

CREATE TABLE wishlist (
  customer_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  PRIMARY KEY (customer_id, product_id)
);

CREATE TABLE wishlist ( customer_id INTEGER NOT NULL, product_id INTEGER NOT NULL, PRIMARY KEY (customer_id, product_id) );

Провери▶ Изпълни

Идеята

ON DELETE CASCADE

След REFERENCES може да кажеш какво да стане, когато редът, към който сочиш, се изтрие. Това е ON DELETE.

CASCADE значи „изтрий и мен“. Явор Пенев (клиент 20) има два любими продукта. Теодора Генова (клиент 19) има един. Явор изтрива профила си:

SQLбаза: магазин

DELETE FROM customers WHERE id = 20;
SELECT * FROM wishlist;

DELETE FROM customers WHERE id = 20; SELECT * FROM wishlist;

▶ ИзпълниВърни заявкатаНова база

Любимите на Явор изчезнаха с него. Тези на Теодора са си там. Списък с любими без клиента няма смисъл, затова тук CASCADE е правилно.

Идеята

SET NULL и RESTRICT

SET NULL значи „остави ме, но без връзка“. Въпросите към продуктите са полезни и за другите купувачи, дори когато авторът си е тръгнал:

SQLбаза: магазин

DELETE FROM customers WHERE id = 20;
SELECT * FROM questions;

DELETE FROM customers WHERE id = 20; SELECT * FROM questions;

▶ ИзпълниВърни заявкатаНова база

Въпросът остана, а customer_id стана NULL: въпрос от бивш клиент.

RESTRICT значи „не пускай изтриването“. Фактурите трябва да се пазят години наред по закон:

SQLбаза: магазин

DELETE FROM customers WHERE id = 20;

DELETE FROM customers WHERE id = 20;

▶ ИзпълниВърни заявкатаНова база

Без ON DELETE поведението е почти същото: изтриването спира. Затова Мария не можа да изтрие профила си. Поръчките са история на магазина и не бива да изчезват.

По-точно, за любопитните

Без ON DELETE правилото е NO ACTION. Разликата с RESTRICT е само кога се проверява: RESTRICT спира веднага, а NO ACTION проверява в края на заявката (или на транзакцията, ако проверката е отложена). Има и ON UPDATE за промяна на ключа, но се ползва рядко, защото първичният ключ почти никога не се сменя. ON DELETE SET DEFAULT също е рядкост.

Виж го

SET NULL срещу NOT NULL

Някой е написал таблицата за въпроси така:

customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE SET NULL

Явор пак изтрива профила си:

SQLбаза: магазин

DELETE FROM customers WHERE id = 20;

DELETE FROM customers WHERE id = 20;

▶ ИзпълниВърни заявкатаНова база

SET NULL иска да запише NULL, а NOT NULL не го пуска. Изтриването спира. SQLite не предупреждава за това при CREATE TABLE, а чак когато някой се опита да изтрие. Колона със SET NULL трябва да може да е празна.

Пробвай

Адресите за доставка

Таблицата addresses пази адресите на клиентите. Когато клиентът изтрие профила си, адресите му трябва да изчезнат заедно с него. Поправи customer_id.

SQLбаза: магазин

CREATE TABLE addresses (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  city TEXT NOT NULL,
  street TEXT NOT NULL
);

CREATE TABLE addresses ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), city TEXT NOT NULL, street TEXT NOT NULL );

Провери▶ Изпълни

Идеята

Проверката трябва да е включена

В курса базата проверява външните ключове. Питаш я с PRAGMA, командата за настройките на SQLite:

SQLбаза: магазин

PRAGMA foreign_keys;

PRAGMA foreign_keys;

▶ ИзпълниВърни заявкатаНова база

1 значи „включено“. Ако я изключиш, REFERENCES вече не пази нищо:

SQLбаза: магазин

PRAGMA foreign_keys = OFF;
INSERT INTO orders (customer_id, ordered_at, status) VALUES (99, '2026-10-25', 'new');
SELECT id, customer_id, status FROM orders WHERE customer_id = 99;

PRAGMA foreign_keys = OFF; INSERT INTO orders (customer_id, ordered_at, status) VALUES (99, '2026-10-25', 'new'); SELECT id, customer_id, status FROM orders WHERE customer_id = 99;

▶ ИзпълниВърни заявкатаНова база

Поръчка на клиент 99, който го няма, влезе без грешка.

По-точно, за любопитните

В истинския SQLite проверката е изключена по подразбиране. Пусни sqlite3 на своя компютър и напиши PRAGMA foreign_keys;: отговорът е 0. Причината е старите програми, писани преди SQLite да проверява външни ключове. Настройката важи само за една връзка с базата. Затова програмата пуска PRAGMA foreign_keys = ON; всеки път, когато отваря базата. Тази страница го прави вместо теб. PostgreSQL, MySQL (с InnoDB) и SQL Server проверяват външните ключове по подразбиране.

Упражнение

Въпросите към продуктите

Таблицата questions има id, product_id, customer_id и body. Когато продуктът се махне от магазина, въпросите за него изчезват. Когато клиентът изтрие профила си, въпросите му остават, но без автор. Добави връзките.

SQLбаза: магазин

CREATE TABLE questions (
  id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  customer_id INTEGER,
  body TEXT NOT NULL
);

CREATE TABLE questions ( id INTEGER PRIMARY KEY, product_id INTEGER NOT NULL, customer_id INTEGER, body TEXT NOT NULL );

Провери▶ Изпълни

Тест

Провери се

1. Таблицата orders има customer_id REFERENCES customers(id) без ON DELETE. Какво става при DELETE на клиент с поръчки?

Грешка: клиентът не се триеПоръчките се трият с негоcustomer_id на поръчките става NULL

2. Клиентът изтрива профила си. Какво е подходящо за неговия списък с любими продукти?

ON DELETE RESTRICTON DELETE SET NULLON DELETE CASCADE

3. Колоната е customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE SET NULL. Какво става при изтриване на клиента?

Редът се триеcustomer_id става NULLГрешка NOT NULL и клиентът не се трие

4. Пускаш скрипт в истински SQLite и INSERT към несъществуващ клиент минава. Защо?

Проверката на външните ключове е изключена по подразбиранеREFERENCES не работи в SQLiteЗащото клиентът ще се появи по-късно

Провери

Започни урока отначало

Свързани уроци

• Нужно отпреди: INSERT: нови редове · DELETE: изтриване на редове · CREATE TABLE: нова таблица · Ограничения: правила за данните
• Продължава в: ALTER TABLE: промяна на таблица · Видове връзки: 1:1, 1:N, M:N · ER диаграма: от описание до схема · Нормализация
• Задачи по темата: Таблици и правила