Keys and constraints

Introduction

A constraint is a rule written into the table's design. The DBMS checks it on every INSERT and UPDATE and refuses any change that breaks it, so bad data never gets in, whichever program writes it.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  age INTEGER CHECK (age >= 13)
);

INSERT INTO users (email, name, age) VALUES ('rahim@mail.com', 'Rahim', 20);
INSERT INTO users (email, name, age) VALUES ('nadia@mail.com', 'Nadia', 19);

-- Age 9 breaks the CHECK rule: refused
INSERT INTO users (email, name, age) VALUES ('kid@mail.com', 'Tuhin', 9);

The last INSERT breaks the CHECK rule, so it fails and the table stays as it was. Each constraint error names the rule: CHECK constraint failed, UNIQUE constraint failed: users.email, NOT NULL constraint failed: ....

Programs have bugs, and people type mistakes. Constraints are the last line of defence: rules the database checks itself, so that no program can store a negative age or an order for a customer who does not exist.

How it works

Keys

• A superkey is any set of columns whose values are different in every row: {id}, {email}, {id, name}.
• A candidate key is a smallest superkey: remove any column and it stops being unique. {id} and {email} are both candidate keys.
• The primary key is the candidate key you choose to identify rows. The others are alternate keys; mark them UNIQUE.
• A composite key has more than one column: in an enrollments table, PRIMARY KEY (student_id, course_code).

Syntax

CREATE TABLE teachers (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  age INTEGER CHECK (age >= 18),
  dept_id INTEGER REFERENCES departments(id) ON DELETE CASCADE
);

Example

A foreign key links a row to a row in another table: teachers.dept_id REFERENCES departments(id). The DBMS then refuses a teacher whose department does not exist, which is called referential integrity.

CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE teachers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept_id INTEGER REFERENCES departments(id)
);

INSERT INTO departments (id, name) VALUES (1, 'CSE'), (2, 'EEE');
INSERT INTO teachers (name, dept_id) VALUES ('Dr. Alam', 1), ('Mr. Hasan', 2);

-- There is no department 9: the foreign key refuses the row
INSERT INTO teachers (name, dept_id) VALUES ('Ms. Rina', 9);

What happens when the parent row is deleted is part of the design:

CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE teachers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept_id INTEGER REFERENCES departments(id) ON DELETE CASCADE
);

INSERT INTO departments (id, name) VALUES (1, 'CSE'), (2, 'EEE');
INSERT INTO teachers (name, dept_id) VALUES ('Dr. Alam', 1), ('Ms. Sultana', 1), ('Mr. Hasan', 2);

-- CSE closes: its two teachers go with it
DELETE FROM departments WHERE name = 'CSE';

SELECT * FROM teachers;

Line by line

1. departments has the two departments, and teachers.dept_id refers to them with ON DELETE CASCADE.
2. Three teachers are inserted: two in CSE and one in EEE.
3. DELETE FROM departments WHERE name = 'CSE'; removes the CSE row.
4. Because of ON DELETE CASCADE, the DBMS also deletes the two teachers whose dept_id pointed to CSE.
5. SELECT * FROM teachers; shows the one teacher left, Mr. Hasan.

Expected result

id  name       dept_id
--  ---------  -------
3   Mr. Hasan  2
(1 row)

In the real world

Banks rely on CHECK constraints to stop negative balances where overdrafts are not allowed, and shops rely on foreign keys to stop orders for products that do not exist. Constraints also document the rules for every developer who reads the schema.

Common mistakes

• Leaving rules to the application only, so another program can still store bad data.
• Choosing a primary key that can change, such as a phone number.
• Forgetting that SQLite checks foreign keys only after PRAGMA foreign_keys = ON;.

Best practices

• Give every table a primary key.
• Mark every alternate key UNIQUE.
• Choose ON DELETE behaviour on purpose, and prefer RESTRICT when deleting the parent would lose important history.

Summary

Constraints such as NOT NULL, UNIQUE, CHECK and PRIMARY KEY keep bad data out. Keys identify rows, foreign keys link tables with referential integrity, and ON DELETE rules decide what happens to child rows.

Mark lesson complete