Lab: try to break the rules

Before you start

Connect with psql shop. Run every attempt inside BEGIN; … ROLLBACK; so the data stays clean. If anything slips through and changes shop, reload shop.sql.

1. Attack the schema

For each bad write, predict which rule stops it, then run it and read the error.

#	Attempt	Rule that stops it (ours)	Yours
1	A customer with no name	NOT NULL on name
2	A product priced at 0	CHECK (price > 0)
3	An order with status 'shiped'	CHECK (status IN (…))
4	The same product twice in order 101	Primary key (order_id, product_id)
5	An order item with quantity 0	CHECK (quantity > 0)
6	An order for customer 99	Foreign key to customers
7	Deleting customer 1, who has orders	Foreign key from orders
8	A second customer with asha@example.com	UNIQUE on email

The statements, if you want them:

INSERT INTO customers (id, name, email, joined_on) VALUES (9, NULL, 'x@example.com', '2025-10-18');
INSERT INTO products (id, name, category, price) VALUES (12, 'Free sticker', 'Accessories', 0);
INSERT INTO orders (id, customer_id, status, ordered_at) VALUES (113, 1, 'shiped', now());
INSERT INTO order_items VALUES (101, 3, 1, 18999.00), (101, 3, 1, 18999.00);
INSERT INTO order_items VALUES (101, 3, 0, 18999.00);
INSERT INTO orders (id, customer_id, status, ordered_at) VALUES (113, 99, 'pending', now());
DELETE FROM customers WHERE id = 1;
INSERT INTO customers (id, name, email, joined_on) VALUES (9, 'Asha R.', 'asha@example.com', '2025-10-18');

2. Find what is not protected

Now look for bad data the schema accepts. Each of these succeeds on shop:

Attempt	Why it gets in
An order item with unit_price 0	There is no check on unit_price
A customer whose email is not-an-email	Nothing checks the email’s format
A customer whose joined_on is in the year 2099	Nothing checks dates are in the past

Pick one and decide whether it deserves a rule.

3. Add a rule to an existing table

Adding a constraint checks every existing row. On clean data it succeeds, and from then on the rule applies:

BEGIN;
ALTER TABLE customers ADD CONSTRAINT email_has_at CHECK (email LIKE '%@%');
INSERT INTO customers (id, name, email, joined_on) VALUES (9, 'No At', 'not-an-email', '2025-10-18');
ROLLBACK;

ALTER TABLE
ERROR:  new row for relation "customers" violates check constraint "email_has_at"

4. Try to add a rule the data already breaks

Make the data violate a rule first, then try to add it:

BEGIN;
UPDATE customers SET email = 'ASHA@example.com' WHERE id = 4;
ALTER TABLE customers ADD CONSTRAINT email_lowercase CHECK (email = lower(email));
ROLLBACK;

ERROR:  check constraint "email_lowercase" of relation "customers" is violated by some row

PostgreSQL will not add a rule that is already broken. On a real table you would first find and fix the offending rows (SELECT * FROM customers WHERE email <> lower(email);), then add the constraint.

What you found

	Ours	Yours
Bad writes stopped in step 1	8 of 8
Bad writes accepted in step 2	3 of 3
Constraint added on clean data	Yes
Constraint added over violating data	Refused

The schema protects exactly the rules someone wrote down, and nothing else.

Check yourself

1. What happens when you add a CHECK constraint to a table that already has rows breaking it?

   Show answer

   PostgreSQL checks the existing rows, finds the violation, and refuses to add the constraint. You must fix the data first.

   I could explain this without looking

2. Name a piece of bad data shop currently accepts, and the constraint that would stop it.

   Show answer

   For example an order item with unit_price 0, stopped by CHECK (unit_price > 0); or an email without @, stopped by CHECK (email LIKE '%@%').

   I could explain this without looking

3. Why might you not add a rule for every possible bad value?

   Show answer

   Some rules are hard to express or change often (such as full email validation), and every constraint is checked on every write. Put in the database the rules that must always hold and are cheap to check; validate the rest in the application.

   I could explain this without looking