Първичен и външен ключ
Накратко: Първичният ключ е колоната, която различава всеки ред на таблицата, а външният ключ е колона, която пази първичния ключ на ред от друга таблица.
Проблем
Чия е поръчката?
В урока „Магазинът“ търсихме с очи кой е поръчал поръчка 60 и какво има в нея. Отговорът беше разпръснат в четири таблици: orders, customers, order_items и products.
Защо магазинът не държи всичко в една голяма таблица? И как една таблица „знае“ кой ред от друга е нейният?
Идеята
Първичен ключ
Почти всяка таблица на магазина има колона id. Изключението, order_items, е в „По-точно“ в края на урока. Числото е различно за всеки ред, не се повтаря и не е празно. По него редът се намира без съмнение:
Такава колона се казва първичен ключ (primary key). Имената не стават за това: двама клиенти може да се казват еднакво, а името може да се смени. Числото остава.
SQLite казва коя колона е първичният ключ. Колоната pk е 1 за нея:
Идеята
Външен ключ
Поръчката не пише името на клиента. Тя пази само неговото id в колоната customer_id:
customers
- PK
idinteger nametext *emailtext *citytextjoined_attext *
orders
- PK
idinteger - FK
customer_idinteger *→ customers ordered_attext *statustext *
orders.customer_id сочи към customers.id. Колона, която пази първичния ключ на ред от друга таблица, се казва външен ключ (foreign key).
Връзките на таблица се виждат с pragma_foreign_key_list:
Един ред, една връзка: от колоната customer_id (from) към колоната id (to) на таблицата customers.
Така данните на Мария (име, имейл) са записани веднъж. Ако тя смени имейла си, той се поправя в един ред на customers, а 12-те ѝ поръчки сочат все към същото id.
Пробвай
Редовете на поръчките
Към кои таблици сочи order_items? Покажи връзките ѝ.
Решение
SELECT * FROM pragma_foreign_key_list('order_items');SELECT * FROM pragma_foreign_key_list('order_items'). Две връзки: product_id сочи към products.id, а order_id към orders.id. Един ред на order_items казва „в тази поръчка има този продукт“.
Виж го
Търсене по номер
С WHERE вече не търсим с очи. Кой е поръчал поръчка 60? Първо поръчката:
customer_id е 1. После клиентът с това id:
Мария Иванова. Работи, но са две заявки, а числото 1 го пренасяш на ръка. За една поръчка е търпимо. За отчет с 60 поръчки и имената на клиентите им не е. Следващият урок събира двете стъпки в една заявка с JOIN.
Пробвай
Чий е отзивът?
Отзив 13 е с оценка 1. Кой го е писал? Покажи само името на клиента.
Решение
SELECT customer_id FROM reviews WHERE id = 13;
SELECT name FROM customers WHERE id = 12;Две стъпки: SELECT customer_id FROM reviews WHERE id = 13 дава 12, а SELECT name FROM customers WHERE id = 12 дава Борис Митев. Проверката гледа последната таблица.
Упражнение
Две задачи
1. Отзивът сочи към две неща. Покажи връзките на таблицата reviews.
Връзките на reviews:
Решение
SELECT * FROM pragma_foreign_key_list('reviews');SELECT * FROM pragma_foreign_key_list('reviews'). customer_id сочи към customers, а product_id към products: кой е писал и за кой продукт.
2. Категориите са дърво: „Кухня“ е подкатегория на нещо. Външният ключ parent_id сочи към ред в същата таблица categories. Намери името на родителската категория на „Кухня“.
Родителската категория на „Кухня“:
Решение
SELECT parent_id FROM categories WHERE name = 'Кухня';
SELECT name FROM categories WHERE id = 2;SELECT parent_id FROM categories WHERE name = 'Кухня' дава 2, а SELECT name FROM categories WHERE id = 2 дава „Дом“. Таблица може да сочи към себе си. В урока за много таблици ще го направиш с една заявка.
По-точно, за любопитните
Първичният ключ може да е от няколко колони. В order_items той е двойката (order_id, product_id): pk е 1 и 2 за частите му.
Външният ключ е и правило: базата отказва поръчка за клиент, който го няма. В SQLite това правило е изключено по подразбиране и се пуска с PRAGMA foreign_keys = ON. Тук в курса е пуснато. В PostgreSQL външните ключове се спазват винаги. Ще ги видиш в действие в модула за промяна на данните.
Тест