Foreign keys

SQL · Unit 12: Designing tables

Full plan
SQL
8 exercises

Unit 7 said this database "rejects an order from customer 99." Last unit you watched that rule stop an order for a product that didn't exist. Where is it written that an order needs a customer and a product that exist?

In the statement that created orders, which is this one. In a real database you read it, and you don't run it again. Running it means dropping the existing table first, and its twelve rows go with it. In this app they come back on the next run.

REFERENCES customers(id) goes after the type, like the other rules. It says every customer_id has to be the id of a row in customers. That's a foreign key, and orders has two.

DROP TABLE orders;
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL
    REFERENCES customers(id),
  product_id INTEGER NOT NULL
    REFERENCES products(id),
  quantity INTEGER NOT NULL,
  placed TEXT NOT NULL
);
SELECT COUNT(*) AS row_count
FROM orders;

Returns

row_count
0

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

1. Multiple choice
The store has eight products, 1 through 8. With this table built, three more statements arrive, one at a time. Which one does the database reject?
2. Complete the code
Now the table stores who each review is from. Ann, customer 1, leaves 5 stars. Fill in the blanks to finish the foreign key.
3. Put the lines in order
Put in order the reviews table with its foreign key to products, a 4-star review of the Headphones (product 7), and the SELECT that shows it. Two lines don't belong.
4. Find the bug
The reviews table is created without an error. When the first review is saved, here SQL answers no such table: main.product, and points at the INSERT. Which line has the problem?
5. Find the case that fails
The store saves a 4-star review of the Headphones, product 7. Whoever built the table wrote the foreign key wrong, and today it doesn't show. Which of these changes, made beforehand, makes the database reject that review?
6. Put the lines in order
Another dry run, which in a real database would be done with ALTER TABLE and without losing anything. The store wants to rebuild customers with a column for the email, one that can't repeat. To keep it short, the dry run leaves out city and age. Put in order the statements that drop whatever needs dropping, create the new table, and query it, still empty. Two lines don't belong.
7. Predict the output
This time customer_id has no NOT NULL. Ann, customer 1, leaves 5 stars, and someone who doesn't say who they are leaves 2. What does the SELECT return?
8. Write the code
The store writes down what it buys from each supplier. The suppliers table is already written. Below it, create purchases with four columns, in this order. id is the usual one. supplier_id is a required integer that points to the id of suppliers. product_id is a required integer that points to the id of products. units is a required integer. Write the type as INTEGER.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →