Skip to content

exercises.md

Latest commit

 

History

History
208 lines (124 loc) · 6.48 KB

exercises.md

File metadata and controls

208 lines (124 loc) · 6.48 KB

Section 05 — Constraints

What you'll practice: protecting a table's data with PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT and CHECK constraints, and adding/removing them with ALTER TABLE.

Constraints are how you turn a table from a "bucket that holds rows" into a proper data contract. A table with good constraints cannot end up in an invalid state, no matter how buggy the application code that talks to it is.

Learning objectives

  • Add a PRIMARY KEY to an existing table
  • Add UNIQUE, CHECK, and NOT NULL constraints
  • Give constraints meaningful names
  • Read constraint violation error messages and understand them
  • DROP CONSTRAINT to remove one
  • Clean up existing bad data before adding a constraint

How to run these exercises

psql -U postgres -d sql_exercise -f 04-advanced-databases/05-constraints/schema.sql
psql -U postgres -d sql_exercise

The reference schema.sql builds the finished table with every constraint in place. We will not load the seed for this section right away — the whole point is to build the table up constraint by constraint. Drop and rebuild without any constraints:

SET search_path TO adv_constraints;

DROP TABLE IF EXISTS employee;
CREATE TABLE employee (
    id         INTEGER,
    email      VARCHAR(255),
    first_name VARCHAR(100),
    last_name  VARCHAR(100),
    salary     NUMERIC(10,2),
    country    CHAR(2),
    joined_on  DATE
);

Now you have a wide-open table. Start the exercises.

Exercises

Exercise 1 — Break it on purpose

With no constraints at all, insert two rows that share the same id, then insert a row where first_name is NULL, then insert a row where salary = -5000.

INSERT INTO employee (id, email, first_name, last_name, salary, country, joined_on)
VALUES (1, 'a@example.com', 'Ada',  'Lovelace', 100,  'GB', '2015-03-02'),
       (1, 'b@example.com', NULL,   'Turing',  -5000, 'GB', '2010-09-01');

Question: do any of these fail? What does that tell you about the default behaviour of a brand-new table?

Exercise 2 — Start fresh

Delete every row so the table is empty again:

TRUNCATE employee;

Exercise 3 — Add a NOT NULL constraint

Use ALTER TABLE to mark first_name, last_name, and email as NOT NULL.

Verify: \d employee and check the "Nullable" column.

Try it: after adding the constraint, re-run the insert from Exercise 1 with NULL first_name. What error do you get?

Exercise 4 — Add a PRIMARY KEY

Add a primary key on id, and give the constraint a name (employee_pk).

Hint: ALTER TABLE employee ADD CONSTRAINT employee_pk PRIMARY KEY (id);

Verify: \d employee. You should see the primary key listed at the bottom.

Question: what does PRIMARY KEY do that adding UNIQUE + NOT NULL separately would not?

Exercise 5 — Add a UNIQUE constraint on email

Add a unique constraint on email, named employee_email_unique.

Try it: insert two employees with the same email. What error do you get? What constraint name appears in the error?

Exercise 6 — Add a CHECK constraint

Add a CHECK constraint named employee_salary_non_negative that enforces salary >= 0.

Try it: insert a row with salary = -100. Confirm it fails with an error that names your constraint.

Exercise 7 — Add another CHECK on country

Add a CHECK constraint that enforces country is exactly two uppercase letters.

Hint: CHECK (country ~ '^[A-Z]{2}$'). ~ is the Postgres regex match operator.

Try it: insert a row with country = 'gb'. Why does it fail?

Exercise 8 — Add DEFAULT values

ALTER the table so:

  • salary defaults to 0
  • country defaults to 'GB'
  • joined_on defaults to CURRENT_DATE

Hint: ALTER TABLE ... ALTER COLUMN salary SET DEFAULT 0;

Verify: run INSERT INTO employee (id, email, first_name, last_name) VALUES (99, 'x@example.com','X','Y');. It should succeed, and the defaults should be applied.

Exercise 9 — Drop the country CHECK constraint

Drop employee_country_iso2. Then insert a row with country = 'gb' to prove it works now.

Hint: ALTER TABLE ... DROP CONSTRAINT ...;

Exercise 10 — Re-add the constraint the hard way

Add the country constraint back. But this time, before you add it, try inserting an invalid row ('gb') first. Then try adding the constraint — it will fail because there's already bad data.

Question: what's the error? What does it tell you about how ALTER TABLE ADD CONSTRAINT validates existing rows?

Exercise 11 — Clean up the bad data, then add the constraint

Update the bad row (country = 'gb') to 'GB', then add the employee_country_iso2 CHECK back. This time it should succeed.

Lesson: in production databases, you very often have to clean up existing data before you can enforce a new constraint.

Exercise 12 — Load the seed data

Now that the table has all its constraints in place, load the seed data:

psql -U postgres -d sql_exercise -f 04-advanced-databases/05-constraints/seed.sql

Verify: SELECT COUNT(*) FROM employee; should return 5.

Exercise 13 — Multi-column UNIQUE

Add a UNIQUE constraint on the combination (first_name, last_name, joined_on), named employee_name_hired_unique. (Not realistic, but it demonstrates a composite unique constraint.)

Verify: \d employee.

Exercise 14 — List every constraint on the table

Use information_schema.table_constraints to list every constraint on the employee table, showing the constraint name and type.

Hint:

SELECT constraint_name, constraint_type
FROM   information_schema.table_constraints
WHERE  table_schema = 'adv_constraints'
  AND  table_name   = 'employee';

Bonus challenges

Bonus 1 — CHECK with multiple columns

Add a CHECK constraint that enforces joined_on <= CURRENT_DATE — you can't hire someone in the future.

Bonus 2 — Conditional UNIQUE (partial index)

A plain UNIQUE treats two NULL emails as distinct. Read about partial unique indexes. Can you enforce "every non-NULL email is unique" without UNIQUE? (You'll need CREATE UNIQUE INDEX ... WHERE email IS NOT NULL.)

Bonus 3 — Validate only new rows

Read \h ALTER TABLE and look up NOT VALID. When would you add a CHECK constraint with NOT VALID, and what does VALIDATE CONSTRAINT do afterwards?

Cleanup

DROP SCHEMA IF EXISTS adv_constraints CASCADE;