Constraints

The full text of this chapter, to read or revisit. To practise, do the exercises.

β–Ά Do the exercises

Exercise 1 β€” Rules the database enforces

A constraint is a rule attached to a column. The database checks it on every INSERT and UPDATE, and refuses anything that breaks it. NOT NULL requires a value; UNIQUE forbids two rows with the same value:

CREATE TABLE gyms ( gym_id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, city TEXT ); INSERT INTO gyms (name, city) VALUES ('Tide Gym', 'Sunset Valley'), ('Ember Gym', 'Azure City'); SELECT * FROM gyms;

Now try to register Tide Gym a second time:

CREATE TABLE gyms ( gym_id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, city TEXT ); INSERT INTO gyms (name, city) VALUES ('Tide Gym', 'Sunset Valley'); INSERT INTO gyms (name, city) VALUES ('Tide Gym', 'Moonlight Hollow'); -- Error: UNIQUE constraint failed: gyms.name

Why bother?

You could check these rules in your application instead. But applications have bugs, and several applications may write to the same database. A constraint is checked every time, by everyone, with no exception.

Exercise 2 β€” DEFAULT and CHECK

DEFAULT gives a column a value when the INSERT does not provide one. CHECK accepts only values that satisfy a condition:

CREATE TABLE recruits ( recruit_id INTEGER PRIMARY KEY, name TEXT NOT NULL, badges INTEGER NOT NULL DEFAULT 0, level INTEGER CHECK (level BETWEEN 1 AND 100) ); INSERT INTO recruits (name, level) VALUES ('Casey', 12); SELECT * FROM recruits;

Casey has 0 badges without anyone typing it. A level of 150, on the other hand, is rejected:

CREATE TABLE recruits ( recruit_id INTEGER PRIMARY KEY, name TEXT NOT NULL, badges INTEGER NOT NULL DEFAULT 0, level INTEGER CHECK (level BETWEEN 1 AND 100) ); INSERT INTO recruits (name, level) VALUES ('Casey', 150); -- Error: CHECK constraint failed: level BETWEEN 1 AND 100

NOT NULL DEFAULT 0

The two go well together: NOT NULL guarantees a value is always there, DEFAULT provides it when nobody does. Counters, flags and statuses are the typical candidates.

Exercise 3 β€” UNIQUE or PRIMARY KEY?

What is the difference between PRIMARY KEY and UNIQUE?

Options
β€’ A table has one PRIMARY KEY, which identifies each row and cannot be NULL; UNIQUE can apply to several other columns
β€’ None: they are two names for the same rule
β€’ UNIQUE is stricter: it also forbids NULL
β€’ PRIMARY KEY only works on TEXT columns

Hint : One key to identify, as many UNIQUE as you need.

Exercise 4 β€” No duplicate gyms

Complete the statement so that gym names cannot repeat and badges_given starts at 0 when not provided.

Hint : One constraint forbids repeats, the other supplies the missing value.

Exercise 5 β€” Foreign keys

A badges table needs a trainer_id column that points to a trainer. Written like this, the database does not know that: to it, trainer_id is just a number. A badge for trainer 99 β€” who does not exist β€” is accepted without a word:

CREATE TABLE badges ( badge_id INTEGER PRIMARY KEY, trainer_id INTEGER NOT NULL, gym TEXT NOT NULL ); INSERT INTO badges (trainer_id, gym) VALUES (99, 'Tide Gym'); SELECT * FROM badges;

Now tell the database what the column means. REFERENCES trainers(trainer_id) declares a foreign key: "this value must be the id of an existing trainer". One SQLite detail: this check is switched off by default, so the script starts with PRAGMA foreign_keys = ON;. Let's try the same insert, now refused:

PRAGMA foreign_keys = ON; CREATE TABLE badges ( badge_id INTEGER PRIMARY KEY, trainer_id INTEGER NOT NULL REFERENCES trainers(trainer_id), gym TEXT NOT NULL ); INSERT INTO badges (trainer_id, gym) VALUES (99, 'Tide Gym'); -- Error: FOREIGN KEY constraint failed

With a real trainer β€” Alex has trainer_id 1 β€” the row goes in:

PRAGMA foreign_keys = ON; CREATE TABLE badges ( badge_id INTEGER PRIMARY KEY, trainer_id INTEGER NOT NULL REFERENCES trainers(trainer_id), gym TEXT NOT NULL ); INSERT INTO badges (trainer_id, gym) VALUES (1, 'Tide Gym'); SELECT * FROM badges;

Parents before children

A foreign key also protects deletes: with the check on, a trainer who still has badges cannot be deleted. So: create trainers before badges, insert the trainer before the badge, delete the badges before the trainer.

Exercise 6 β€” Five rules

Match each constraint with what it guarantees.

Items to match
β€’ NOT NULL
β€’ UNIQUE
β€’ DEFAULT 0
β€’ CHECK (level > 0)
β€’ REFERENCES trainers(trainer_id)
Definitions
β€’ A value is required
β€’ No two rows share the value
β€’ The value used when none is given
β€’ The value must satisfy a condition
β€’ The value must exist in another table

Exercise 7 β€” Read the refusal

In gyms, name is declared TEXT NOT NULL UNIQUE. You insert 'Tide Gym' twice. What happens on the second INSERT?

Options
β€’ It is refused: UNIQUE constraint failed: gyms.name
β€’ It is refused: NOT NULL constraint failed: gyms.name
β€’ It replaces the first row
β€’ It is accepted: two rows named Tide Gym

Hint : The value is there, it is just not unique.

Exercise 8 β€” A solid badges table

Professor Pi wants a badges table that cannot hold nonsense.

Turn foreign key checks on, then create badges with badge_id (automatic id), trainer_id (required, must reference trainers), gym (required text) and earned_on (text, default '2023-01-01'). Insert a badge for Alex at Tide Gym earned on 2023-01-20 and one for Morgan at Ember Gym with no date. Finish by showing the trainer's name as trainer, plus gym and earned_on, sorted by badge_id.

Starter code
-- Write your statements below, and finish with a SELECT to check the result
Back to the course