Constraints

SQL · Unit 12: Designing tables

Full plan
SQL
11 exercises

Last unit's project left a -1 stored in the Monitors' stock, and a sentence: "Nothing in this table stops it from storing a negative stock." So who was supposed to stop it?

What a table rejects and what it lets through is decided in its CREATE TABLE. Each rule is written after its column's type, and SQL calls them constraints.

Last lesson's suppliers table had none. Here are the first two. PRIMARY KEY makes id the primary key, which doesn't repeat. NOT NULL makes a column required.

CREATE TABLE suppliers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  phone TEXT
);
INSERT INTO suppliers
  (name, phone)
VALUES
  ('Summit', '6175550142'),
  ('Techline', '2125550188');
SELECT id, name, phone
FROM suppliers;

Returns

id	name	phone
1	Summit	6175550142
2	Techline	2125550188

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
With this table built and Summit in it, three more suppliers arrive, each in its own INSERT. Which one does the database reject?
2. Complete the code
Breeze is added with only its name. Fill in the blanks so that the table gives Breeze its id and accepts nobody without a name.
3. Find the bug
Two suppliers, Summit and Breeze, are added in a single INSERT. Here SQL answers UNIQUE constraint failed: suppliers.email. Which line has the problem?
4. Predict the output
Breeze and Techline haven't given their email yet, and they're added without it. The email column is UNIQUE, and you've known since unit 4 that to SQL one NULL isn't equal to another NULL. What does the SELECT return?
5. Predict the output
Last unit, writing NULL and not naming the column gave the same result, because those tables had no DEFAULT. This one does. Whoever adds Techline doesn't know how long it takes, and types in a NULL by hand for days. Breeze is added without naming that column. What does the SELECT return?
6. Multiple choice
There's 1 Monitor left and someone sells 2. With this table, along comes UPDATE warehouse SET stock = stock - 2 WHERE id = 1;. What happens?
7. Find the bug
Someone rewrites the stock rule and loads two products: the Monitor, with 5, and the Cable, which is out of stock. Here SQL answers CHECK constraint failed: stock > 0. The Cable's row is telling the truth. Which line has the problem?
8. Put the lines in order
The store writes down what it orders from its suppliers. A restock order starts out pending, without anyone having to type it. Put in order the table, a restock order for Cables, and the SELECT that shows it. Two lines don't belong.
9. Find the case that fails
orders was created with quantity INTEGER NOT NULL, and no other rule. The owner counts the orders placed by Luke, customer 2, which today come to 3. Which of these changes, all of which the table accepts without complaint, makes the count include an order that isn't one?
10. Multiple choice
Each supplier has a registration number. The store doesn't want two suppliers with the same number, or one without a number. The table already has its id. How is that column declared?
11. Write the code
Create the suppliers table with all its rules. It has four columns. id is the usual id INTEGER PRIMARY KEY. name is required text. email is text that can't repeat. days is an integer that's 7 unless someone says otherwise, and it can't be negative. To check it, the app tries to put seven rows in, and only four follow the rules.

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 →