When the Database Says No

Several times in this lesson, the database has refused a change. Such errors come from constraints: rules that a table sets for its data. The database checks them on every INSERT and UPDATE (and the foreign keys on every DELETE too), and when a statement would break one of them, it refuses the whole statement and changes nothing. The rules are part of the database, so they protect the data whoever writes it: the shop's website, its warehouse app, or you.

These are the constraints of Pixel Shop, each with an example of its message:

NOT NULL: the column must have a value. In Pixel Shop, only a game's release date, a customer's city and referrer, and a review's comment may be empty. A game without an age rating gets NOT NULL constraint failed: games.age_rating.
UNIQUE: no two rows may have the same value in the column. Every customer has an email of their own, so a second customer with emma.clark@mail.com gets UNIQUE constraint failed: customers.email. A primary key is unique too: an ID that's already taken gets UNIQUE constraint failed: games.id, and since the primary key of a review is the game and the customer together, each customer can review each game only once.
CHECK: a condition that every row must meet. In Pixel Shop: stars from 1 to 5, a quantity greater than 0, and the status paid or refunded. The message shows the condition: CHECK constraint failed: stars BETWEEN 1 AND 5.
FOREIGN KEY: a foreign key must point at a row that exists. A review by customer 99 gets FOREIGN KEY constraint failed, and so does deleting a row that other rows point at. This message doesn't say which column it's about.
Types: in STRICT tables, an INTEGER column holds only whole numbers and a TEXT column only text. Text in a number column gets cannot store TEXT value in INTEGER column reviews.stars. A whole number in quotes, such as '5', is turned into the number 5 without a problem, but 'five' or 4.5 are refused.

When several rows are added at once and one of them breaks a rule, none of them gets in:

INSERT INTO reviews (game_id, customer_id, stars)
VALUES
    (4, 4, 5),
    (14, 2, 6);

Error on line 1: CHECK constraint failed: stars BETWEEN 1 AND 5

The first review is fine, but the second one has 6 stars, so the database refuses the whole statement, and the first review isn't added either.

When a statement breaks more than one rule, the database tells you about one of them. Fix it and run the statement again, and you may get the next one. The table and the column in the message tell you where to look. And where do the rules come from? Each table has them in its definition, the statement CREATE TABLE that created it. In the next lesson, you'll write your own tables, constraints and all.

Typical mistake: getting the error out of the way instead of fixing the data. When a new customer gets UNIQUE constraint failed: customers.email, the fix isn't to change a letter of the email until the database accepts it. The customer may already be in the table, or the email may be wrong. The constraint has found a real problem, so find out what it is.

Instructions

The INSERT in the editor should add a new customer, Ruby Ward, but the database refuses it. Run it, read the error, fix the INSERT and run it again, until it works: it has two mistakes. Ruby joined on 2026-01-12, and the email in the INSERT is Emma Clark's; Ruby's own is ruby.ward@mail.com. The query below then shows her:

id	name	email	city	joined
15	Ruby Ward	ruby.ward@mail.com	Leeds	2026-01-12

Ruby places her first order. Its INSERT isn't in the editor yet, and it has mistakes too. Copy it to the end of the file, below the query, run the file and fix the INSERT until the database accepts it. The order is Ruby's, and it's paid:

INSERT INTO orders (customer_id, ordered_at, status)
VALUES (51, '2026-01-12', 'new');

Then show the ID, customer, date and status of Ruby's orders:

id	customer_id	ordered_at	status
31	15	2026-01-12	paid

And the order's item: one copy of Moon Farm (game 3) for $20. Copy this INSERT to the end of the file too, and fix its mistakes:

INSERT INTO order_items (order_id, game_id, quantity, price)
VALUES (31, 3, 0, '$20');

Then show all the columns of the items of order 31:

order_id	game_id	quantity	price
31	3	1	20

Start programming for free

7/9