Description

Login
Back

Intermediate · 18 min

Keys and constraints

Enforce required values, unique identifiers, and valid relationships.

Sample database

CREATE TABLE groups (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, group_id INTEGER REFERENCES groups(id));
CREATE TABLE sessions (id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES members(id), topic TEXT NOT NULL, minutes INTEGER NOT NULL CHECK(minutes > 0), completed INTEGER NOT NULL CHECK(completed IN (0, 1)), day TEXT NOT NULL);
INSERT INTO groups VALUES (1, 'Morning'), (2, 'Evening');
INSERT INTO members VALUES (1, 'Ada', 'Oslo', 1), (2, 'Bo', 'Rome', 1), (3, 'Cy', NULL, 2), (4, 'Dee', 'Oslo', 2);
INSERT INTO sessions VALUES
  (1, 1, 'Travel', 20, 1, '2026-01-01'),
  (2, 1, 'Food', 10, 0, '2026-01-02'),
  (3, 2, 'Travel', 30, 1, '2026-01-01'),
  (4, 2, 'Music', 15, 1, '2026-01-03'),
  (5, 3, 'Music', 25, 0, '2026-01-02'),
  (6, 3, 'Travel', 15, 1, '2026-01-04');

Define a constrained table

A schema can reject invalid data independently of application code. Use NOT NULL with CHECK because a CHECK expression evaluating to NULL is not a failure in SQLite. Primary keys and unique constraints address different identification needs.

CREATE TABLE goals (id INTEGER PRIMARY KEY, label TEXT NOT NULL UNIQUE, minutes INTEGER NOT NULL CHECK(minutes > 0));
INSERT INTO goals VALUES (1, 'Weekly', 60);
SELECT label, minutes FROM goals;

Output

label | minutes
Weekly | 60

Understand conflict handling

ON CONFLICT can handle a specific uniqueness conflict. It does not make every invalid write valid, and foreign-key errors still fail. The playground enables SQLite foreign keys before loading the sample database.

INSERT INTO members (id, name, city, group_id) VALUES (4, 'Other', 'Paris', 2) ON CONFLICT(id) DO NOTHING;
SELECT name FROM members WHERE id = 4;

Output

name
Dee

Put it into practice

  1. Read the sample tables and predict the result before running the query.
  2. Solve the task, compare the returned rows, and explain why the solution works.

Try it yourself

Code runs on this device. When you are signed in, drafts sync to your account.

Run queries on a fresh sample database. Table changes last for this run only; your query draft is saved separately. Results show column names followed by rows. NULL means a missing value.

Apply what you learned

Create a goals table with the given constraints and insert a Weekly goal lasting 60 minutes, then read its label and duration.

Show solution
CREATE TABLE goals (id INTEGER PRIMARY KEY, label TEXT NOT NULL UNIQUE, minutes INTEGER NOT NULL CHECK(minutes > 0));
INSERT INTO goals VALUES (1, 'Weekly', 60);
SELECT label, minutes FROM goals;

The positive duration passes CHECK, and the required label and primary key are supplied.

Practice