SQL Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, and NOT NULL

SQL constraints prevent invalid data from entering a database. PRIMARY KEY, FOREIGN KEY, UNIQUE, and NOT NULL identify rows, preserve relationships, reject duplicates, and require values.

The examples use SQLite, including the places where its behavior differs from other SQL databases.

This article covers the "Constraints" chapter of Learn SQL, where you can write and run the queries yourself.

SQL Constraints Explained

A constraint is a rule we create on a database that enforces some specific behavior. For example, setting a NOT NULL constraint on a column ensures that the column will not accept NULL values.

If we try to INSERT a NULL value into a column with the NOT NULL constraint, the insert will fail with an error message. An UPDATE that would break the rule fails the same way. Constraints are extremely useful when we need to ensure that certain kinds of data exist within our database.

Application validation can return better error messages, but it can't replace database constraints. Data arrives through scripts, migrations, admin tools, background jobs, and multiple application servers. All those writes eventually meet at the database.

Constraints are part of a database's schema. A database's schema describes how data is organized within it. Data types, table names, field names, constraints, and the relationships between all of those entities are part of a database's schema. Schema changes usually belong in repeatable database migrations so every environment applies the same rules.

When designing a database schema, there typically isn't a "correct" solution. We do our best to choose a reasonable set of tables, fields, constraints, etc. that will accomplish our project's goals. Like many things in programming, different schema designs come with different trade-offs.

SQLite supports PRIMARY KEY, UNIQUE, NOT NULL, CHECK, and FOREIGN KEY constraints.

How the NOT NULL Constraint Works

In SQL, a cell with a NULL value indicates that the value is missing. A NULL value is very different from a zero value. It also isn't an empty string or false.

The NOT NULL constraint can be added directly to the CREATE TABLE statement.

CREATE TABLE employees(
  id INTEGER PRIMARY KEY,
  -- The PRIMARY KEY constraint uniquely identifies each row in the table
  name TEXT UNIQUE,
  -- The UNIQUE constraint ensures that no two rows can have the same value in the 'name' column
  title TEXT NOT NULL
  -- The NOT NULL constraint ensures that the 'title' column cannot have NULL values
);

This insert fails because title has no value:

INSERT INTO employees (id, name, title)
VALUES (1, 'Lane', NULL);

Use NOT NULL when a row doesn't make sense without the value. Optional fields such as a user's biography or deletion timestamp should remain nullable.

A default value doesn't make a nullable column required. DEFAULT supplies a value only when an insert omits the column. An explicit NULL still violates NOT NULL.

What Does a PRIMARY KEY Do?

A key defines and protects relationships between tables. A primary key is a special column that uniquely identifies records within a table. Each table can have one, and only one primary key, although that key can contain multiple columns.

It's very common to have a column named id on each table in a database, and that id is the primary key for that table. No two rows in that table can share an id.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username TEXT NOT NULL
);

A PRIMARY KEY constraint can be explicitly specified on a column to ensure uniqueness, rejecting any inserts where you attempt to create a duplicate ID.

SQLite gives INTEGER PRIMARY KEY special treatment. In a normal rowid table, a column with that exact declaration becomes an alias for SQLite's internal rowid. If an insert omits the value or supplies NULL, SQLite assigns an integer. INT PRIMARY KEY doesn't behave this way; the type must be exactly INTEGER.

SQLite also has a legacy exception: most non-integer primary keys can contain NULL unless the table is STRICT, uses WITHOUT ROWID, or the column also has NOT NULL. Declare NOT NULL explicitly for a text primary key if you aren't using one of those table modes.

Integer IDs are common. UUIDs can also serve as primary keys when the system needs globally unique identifiers.

UNIQUE vs. PRIMARY KEY

A UNIQUE constraint ensures that no two rows can have the same value in a column (or combination of columns). Unlike a primary key, a table can have multiple unique constraints.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL UNIQUE
);

The id identifies the row. email and username are alternate values that must also remain unique.

SQLite considers each NULL distinct for a UNIQUE constraint, so several rows can have NULL in a unique column. Add NOT NULL when you need every row to contain a unique, present value. TEXT UNIQUE NOT NULL is a common combination for usernames and email addresses.

You can also enforce uniqueness across multiple columns:

CREATE TABLE enrollments (
  student_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  UNIQUE (student_id, course_id)
);

A student can enroll in many courses, but the same student-course pair can't appear twice.

FOREIGN KEY Enforcement

Foreign keys are what make relational databases relational! Foreign keys define the relationships between tables. Simply put, a FOREIGN KEY is a field in one table that references the PRIMARY KEY (or a UNIQUE column) of another table. It protects referential integrity, which means references between tables remain valid.

Creating a FOREIGN KEY in SQLite happens at table creation! After we define the table fields and constraints we add a named CONSTRAINT where we define the FOREIGN KEY column and its REFERENCES.

Here's an example:

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

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER,
  CONSTRAINT fk_departments
    FOREIGN KEY (department_id)
    REFERENCES departments(id)
);

In this example, an employee has a department_id. The department_id must be the same as the id field of a record from the departments table. fk_departments is the specified name of the constraint.

1. CONSTRAINT fk_departments: create a constraint called fk_departments
2. FOREIGN KEY (department_id): make this constraint a foreign key assigned to the department_id field
3. REFERENCES departments(id): link the foreign field id from the departments table

Creating a foreign key without the CONSTRAINT keyword works too; it just means the name of the constraint is auto-assigned.

The child column doesn't need to be unique because many employees can belong to one department. Notice that department_id is also nullable here: a NULL foreign key means "no related row." Combine the foreign key with NOT NULL when the relationship is required.

SQLite has one critical setup detail: foreign-key enforcement must be enabled for each database connection:

PRAGMA foreign_keys = ON;

Don't assume it's enabled. A foreign key that isn't enforced won't protect the data.

Foreign keys also need a deletion policy. SQLite's ON DELETE actions include NO ACTION, RESTRICT, SET NULL, SET DEFAULT, and CASCADE. Choose based on what should happen to child rows when their parent disappears. Don't add CASCADE until you're sure all dependent data should be deleted.

Combining SQL Constraints

Columns often have more than one requirement, so real schemas combine constraints:

A CHECK constraint requires its expression to evaluate to true or NULL. SQLite rejects a row when the expression evaluates to zero. Use NOT NULL as well when NULL should fail the rule.

PRAGMA foreign_keys = ON;

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username TEXT NOT NULL UNIQUE
);

CREATE TABLE transactions (
  id INTEGER PRIMARY KEY,
  sender_id INTEGER NOT NULL,
  memo TEXT NOT NULL,
  amount_cents INTEGER NOT NULL CHECK (typeof(amount_cents) = 'integer' AND amount_cents > 0),
  FOREIGN KEY (sender_id) REFERENCES users(id)
);

The constraints give each column a specific rule:

• transactions.id uniquely identifies a transaction.
• users.username must be present and can't be duplicated.
• transactions.sender_id must be present and refer to an existing user.
• transactions.memo and transactions.amount_cents must be present.
• transactions.amount_cents must be a positive integer amount in cents.

The CHECK constraint keeps the domain rule in the database, so every writer must supply a positive integer amount. The typeof() test matters because normal SQLite tables use dynamic typing. Storing cents as an integer also avoids floating-point rounding errors.

Constraints also make database-backed tests more useful. A test that writes to a real database catches schema violations that a hand-built substitute can easily miss.

Adding Constraints to an Existing SQLite Table

In other dialects of SQL you can ADD CONSTRAINT within an ALTER TABLE statement. For most of its history, SQLite did not support this feature, so when we create our tables we need to make sure we specify all the constraints we want!

SQLite's ALTER TABLE support remains limited and version-dependent. SQLite 3.53.0 added direct support for adding and removing CHECK and NOT NULL constraints:

CREATE TABLE pending_transactions (
  id INTEGER PRIMARY KEY,
  memo TEXT,
  amount_cents INTEGER
);

ALTER TABLE pending_transactions
ADD CONSTRAINT positive_amount
CHECK (typeof(amount_cents) = 'integer' AND amount_cents > 0);

ALTER TABLE pending_transactions
ALTER COLUMN memo SET NOT NULL;

Earlier versions don't support these operations directly. Even in SQLite 3.53 and later, this isn't general-purpose ADD CONSTRAINT support: adding a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint to existing columns still requires another approach.

For unsupported changes, rebuild the table. The SQLite CREATE TABLE guide covers the reconstruction procedure and its safety requirements.

Run the change in a transaction, check rows before applying the new rule, and test against a backup. Adding NOT NULL, CHECK, or UNIQUE to dirty data will fail until you fix invalid values.

SQLite does allow some constraints when adding a new column, but with restrictions. For example, a new NOT NULL column needs a non-NULL default, and a column added with REFERENCES must default to NULL when foreign keys are enabled. Check the current SQLite documentation rather than assuming another database's ALTER TABLE syntax will work.