Constraints
The full text of this chapter, to read or revisit. To practise, do the exercises.
βΆ Do the exercisesExercise 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.nameWhy 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 100NOT 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?
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 failedWith 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.
NOT NULLUNIQUEDEFAULT 0CHECK (level > 0)REFERENCES trainers(trainer_id)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?
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.
-- Write your statements below, and finish with a SELECT to check the result