Primary key and foreign key in SQL

Problem

Whose order is it?

In the lesson “The shop” we searched by eye for who placed order 60 and what was in it. The answer was spread across four tables: orders, customers, order_items and products.

Why does the shop not keep everything in one big table? And how does a table “know” which row in another table is its own?

The idea

Primary key

Almost every table in the shop has an id column. The exception, order_items, is in the note at the end of the lesson. The number is different for every row, never repeats and is never empty. A row can be found by it without any doubt:

SQLdatabase: shop

SELECT id, name, city FROM customers WHERE id = 1;

Such a column is called a primary key. Names will not do: two customers can have the same name, and a name can change. The number stays.

SQLite tells you which column is the primary key. The pk column is 1 for it:

SQLdatabase: shop

SELECT name, pk FROM pragma_table_info('customers');

The idea

Foreign key

An order does not store the customer name. It keeps only their id, in the customer_id column:

customers

PKidinteger
nametext *
emailtext *
citytext
joined_attext *

orders

PKidinteger
FKcustomer_idinteger *→ customers
ordered_attext *
statustext *

orders.customer_id points to customers.id. A column that keeps the primary key of a row in another table is called a foreign key.

A table's links are shown by pragma_foreign_key_list:

SQLdatabase: shop

SELECT * FROM pragma_foreign_key_list('orders');

One row, one link: from the customer_id column (from) to the id column (to) of the customers table.

So Maria's details (name, email) are stored once. If she changes her email, it is fixed in one row of customers, and her 12 orders still point to the same id.

Try it

The order lines

Which tables does order_items point to? Show its links.

SQLdatabase: shop

SELECT * FROM pragma_foreign_key_list('orders');

Solution

SELECT * FROM pragma_foreign_key_list('order_items');

See it

Looking up by number

With WHERE we no longer search by eye. Who placed order 60? First the order:

SQLdatabase: shop

SELECT id, customer_id, ordered_at, status FROM orders WHERE id = 60;

customer_id is 1. Then the customer with that id:

SQLdatabase: shop

SELECT id, name, email FROM customers WHERE id = 1;

Maria Ivanova. It works, but it takes two queries, and you carry the number 1 over by hand. For one order that is bearable. For a report of 60 orders with their customers' names it is not. The next lesson puts the two steps into one query with JOIN.

Try it

Whose review is it?

Review 13 has a rating of 1. Who wrote it? Show only the customer name.

SQLdatabase: shop

SELECT * FROM reviews WHERE id = 13;

Solution

SELECT customer_id FROM reviews WHERE id = 13;
SELECT name FROM customers WHERE id = 12;

Exercise

Two tasks

1. A review points to two things. Show the links of the reviews table.

The links of reviews:

SQLdatabase: shop

Solution

SELECT * FROM pragma_foreign_key_list('reviews');

2. The categories form a tree: “Kitchen” is a subcategory of something. The foreign key parent_id points to a row in the same categories table. Find the name of the parent category of “Kitchen”.

The parent category of “Kitchen”:

SQLdatabase: shop

Solution

SELECT parent_id FROM categories WHERE name = 'Kitchen';
SELECT name FROM categories WHERE id = 2;

More precisely, if you are curious

A primary key can be made of several columns. In order_items it is the pair (order_id, product_id): pk is 1 and 2 for its parts.

SQLdatabase: shop

SELECT name, pk FROM pragma_table_info('order_items');

A foreign key is also a rule: the database refuses an order for a customer who does not exist. In SQLite this rule is off by default and is turned on with PRAGMA foreign_keys = ON. Here in the course it is on. In PostgreSQL foreign keys are always enforced. You will see them at work in the module on changing data.

Test

Check yourself

1. What is true about the primary key of customers?It can repeat if the names are differentIt is the customer nameEvery customer has their own id, which never repeats and is never empty

2. orders.customer_id is 4. What does that mean?That the order belongs to the customer with id 4 in customersThat the order has 4 productsThat it is the customer’s fourth order

3. Why is the customer name not stored in each of their orders?Because SQL does not allow text in ordersTo save space and avoid mismatches: the name is kept in one placeBecause orders are anonymous

4. The primary key of order_items is (order_id, product_id). What does that forbid?An order having two productsThe same product being on two rows of one orderA product being in two orders

Primary key and foreign key

Cookies