SQLite Constraints

Constraints are rules you attach to columns or tables that SQLite enforces automatically. They prevent bad data from entering the database. Think of constraints as a bouncer at the door — they check each incoming row and reject anything that breaks the rules.

Available Constraints in SQLite

CONSTRAINT       PURPOSE
────────────────────────────────────────────────────────
NOT NULL         Column must always have a value
UNIQUE           No two rows can have the same value
PRIMARY KEY      Uniquely identifies each row
CHECK            Value must satisfy a condition
DEFAULT          Provides a value when none is given
FOREIGN KEY      Links a column to another table

NOT NULL Constraint

A NOT NULL constraint ensures a column always receives a real value. Without it, the column accepts NULL by default.

CREATE TABLE employees (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,   ← name is required
  dept  TEXT             ← dept is optional (NULL allowed)
);

-- This INSERT works:
INSERT INTO employees (name) VALUES ('Alice');

-- This INSERT FAILS:
INSERT INTO employees (dept) VALUES ('HR');
-- Error: NOT NULL constraint failed: employees.name

UNIQUE Constraint

A UNIQUE constraint prevents duplicate values in a column. Two rows cannot hold the same value in a UNIQUE column — except NULL, which is treated as unknown and can repeat.

CREATE TABLE users (
  id    INTEGER PRIMARY KEY,
  email TEXT UNIQUE,
  name  TEXT
);

INSERT INTO users (email, name) VALUES ('a@x.com', 'Alice');
INSERT INTO users (email, name) VALUES ('b@x.com', 'Bob');

-- This FAILS (duplicate email):
INSERT INTO users (email, name) VALUES ('a@x.com', 'Carol');
-- Error: UNIQUE constraint failed: users.email

-- Two NULLs are allowed (NULL ≠ NULL in SQLite):
INSERT INTO users (name) VALUES ('Dan');   -- email = NULL
INSERT INTO users (name) VALUES ('Eve');   -- email = NULL ✓

PRIMARY KEY Constraint

Every table should have a PRIMARY KEY. It uniquely identifies each row. In SQLite, an INTEGER PRIMARY KEY column auto-increments — SQLite assigns the next available number automatically.

CREATE TABLE products (
  id    INTEGER PRIMARY KEY,   ← auto-number
  name  TEXT NOT NULL
);

INSERT INTO products (name) VALUES ('Pen');
INSERT INTO products (name) VALUES ('Book');

sqlite> SELECT * FROM products;
id │ name
───┼──────
1  │ Pen
2  │ Book

IDs assigned automatically ↑

Composite Primary Key

A composite primary key uses two or more columns together as the unique identifier.

CREATE TABLE enrollments (
  student_id INTEGER,
  course_id  INTEGER,
  grade      REAL,
  PRIMARY KEY (student_id, course_id)
);
-- One student can enroll in many courses,
-- but not the same course twice.

CHECK Constraint

A CHECK constraint validates a column value against a condition you define. SQLite rejects any row where the condition evaluates to false.

CREATE TABLE students (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age  INTEGER CHECK(age >= 5 AND age <= 120),
  gpa  REAL    CHECK(gpa >= 0.0 AND gpa <= 4.0)
);

-- Valid insert:
INSERT INTO students (name, age, gpa) VALUES ('Alice', 20, 3.5);

-- FAILS — age too low:
INSERT INTO students (name, age, gpa) VALUES ('Bob', 2, 3.0);
-- Error: CHECK constraint failed: students

-- FAILS — GPA out of range:
INSERT INTO students (name, age, gpa) VALUES ('Carol', 19, 5.0);
-- Error: CHECK constraint failed: students

DEFAULT Constraint

A DEFAULT value fills in a column when you omit that column during an insert. Without a default, omitted columns receive NULL.

CREATE TABLE orders (
  id         INTEGER PRIMARY KEY,
  product    TEXT NOT NULL,
  quantity   INTEGER DEFAULT 1,
  status     TEXT    DEFAULT 'pending',
  created_at TEXT    DEFAULT (date('now'))
);

-- Insert without quantity, status, created_at:
INSERT INTO orders (product) VALUES ('Notebook');

sqlite> SELECT * FROM orders;
id │ product    │ quantity │ status  │ created_at
───┼────────────┼──────────┼─────────┼────────────
1  │ Notebook   │ 1        │ pending │ 2024-07-24
                   ↑ default  ↑ default  ↑ default

FOREIGN KEY Constraint

A foreign key links a column in one table to a primary key in another. It prevents rows that reference nonexistent records.

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

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

-- Must enable FK enforcement (off by default):
PRAGMA foreign_keys = ON;

-- Works — department 1 exists:
INSERT INTO departments VALUES (1, 'Engineering');
INSERT INTO employees (name, dept_id) VALUES ('Alice', 1);

-- FAILS — department 99 does not exist:
INSERT INTO employees (name, dept_id) VALUES ('Bob', 99);
-- Error: FOREIGN KEY constraint failed

Constraint Diagram

CREATE TABLE students (
  id   INTEGER  PRIMARY KEY,   ← unique, auto-number
  name TEXT     NOT NULL,      ← required
  age  INTEGER  CHECK(age>0),  ← must be positive
  code TEXT     UNIQUE,        ← no duplicates
  dept TEXT     DEFAULT 'Gen'  ← fallback value
);

           Incoming Row
               │
       ┌───────▼─────────┐
       │ PRIMARY KEY     │ ← duplicate? REJECT
       │ NOT NULL check  │ ← missing name? REJECT
       │ CHECK condition │ ← age ≤ 0? REJECT
       │ UNIQUE check    │ ← code exists? REJECT
       └───────┬─────────┘
               │ Passed all rules
               ▼
          Row stored ✓

Key Takeaways

• NOT NULL prevents empty values in required columns
• UNIQUE blocks duplicate values; NULL is exempt
• PRIMARY KEY on INTEGER columns auto-numbers rows
• CHECK validates values against a custom condition
• DEFAULT supplies a value when none is provided during insert
• Foreign keys require PRAGMA foreign_keys = ON to be enforced