Offensive Security Researcher & Quantum Cryptography Analyst

Types of Constraints in SQLite: A Complete Guide With Practical Examples

Abdul Wahab Junaid
Types of Constraints in SQLite

Every database I’ve ever built has, at some point, needed rules — rules about what data is allowed in, what must always be present, what has to be unique, and how different tables relate to one another. That’s exactly what constraints are for. In this article, I want to walk you through every major type of constraint SQLite supports, exactly how to write the syntax for each, and plenty of practical examples so you can start enforcing solid data integrity rules in your own databases right away.

What Is a Constraint?

A constraint is a rule attached to a column or a table that restricts the kind of data that can be stored there. Constraints are your first line of defense against bad data — instead of relying entirely on your application code to validate everything (which can fail, have bugs, or simply be bypassed by someone querying the database directly), constraints enforce rules at the database level itself, guaranteeing consistency no matter what tool or script is writing to the data.

SQLite supports the following major constraint types, and I’ll cover each one in depth: PRIMARY KEY, NOT NULL, UNIQUE, CHECK, FOREIGN KEY, and DEFAULT (which isn’t strictly a constraint in the purest sense, but is closely related and commonly discussed alongside them).

PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each row in a table. Every table should have one, and in most well-designed schemas, it’s the very first thing you define.

Syntax

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

In SQLite specifically, when a column is declared as INTEGER PRIMARY KEY, it becomes an alias for SQLite’s internal rowid, which gives you automatic auto-incrementing behavior in most practical cases without needing to write AUTOINCREMENT explicitly.

INSERT INTO customers (name) VALUES ('Ahmed Khan');
INSERT INTO customers (name) VALUES ('Fatima Sheikh');

Since we didn’t provide an id, SQLite automatically assigns the next available integer.

AUTOINCREMENT

If you specifically need SQLite to guarantee that primary key values are never reused, even after rows are deleted, you can add the AUTOINCREMENT keyword:

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

Without AUTOINCREMENT, SQLite might reuse an id value that was previously used by a deleted row (specifically, it may reuse the highest rowid that has been deleted). With AUTOINCREMENT, SQLite tracks the highest id ever used in a separate internal table (sqlite_sequence) and guarantees new rows always get a higher value, never a reused one. I only reach for AUTOINCREMENT when I genuinely need that guarantee — for instance, if I’m using the id in externally shared references (like URLs or invoice numbers) where reusing a number after deletion could cause real confusion. For most typical applications, plain INTEGER PRIMARY KEY is sufficient and slightly more efficient.

Composite Primary Keys

A table can also have a primary key made up of multiple columns together:

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrolled_on TEXT,
    PRIMARY KEY (student_id, course_id)
);

This ensures that the combination of student_id and course_id is unique — meaning a student can’t be enrolled in the same course twice, but different students can share the same course_id, and the same student can appear multiple times with different course_id values.

NOT NULL

The NOT NULL constraint requires that a column always have a value — it cannot be left empty (NULL).

Syntax

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    phone_number TEXT
);

Here, name and email are required fields — any attempt to insert or update a row leaving either of these as NULL will be rejected. Meanwhile, phone_number is optional.

INSERT INTO employees (id, name, email) VALUES (1, 'Bilal Ahmed', 'bilal@example.com');
-- Works fine, phone_number defaults to NULL

INSERT INTO employees (id, email) VALUES (2, 'noemail@example.com');
-- Fails: name is required (NOT NULL) but wasn't provided

I recommend applying NOT NULL liberally to any column that genuinely should never be empty in a well-formed row. It’s a small addition to your schema that prevents an enormous class of downstream bugs.

UNIQUE

The UNIQUE constraint ensures that all values in a column (or combination of columns) are distinct across the entire table — no two rows can share the same value.

Syntax

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

INSERT INTO users (username, email) VALUES ('sana123', 'sana@example.com');
INSERT INTO users (username, email) VALUES ('sana123', 'different@example.com');
-- Fails: username 'sana123' already exists

Composite UNIQUE Constraints

Just like with primary keys, you can apply a UNIQUE constraint across a combination of columns:

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    semester TEXT,
    UNIQUE (student_id, course_id, semester)
);

This ensures a student can’t be enrolled twice in the same course during the same semester, while still allowing them to retake the course in a different semester.

A Reminder About NULL and UNIQUE

Worth repeating here: NULL values are not considered equal to each other under a UNIQUE constraint. You can insert multiple rows with NULL in a UNIQUE column, and SQLite won’t treat them as duplicates, because NULL represents “unknown,” and two unknown values can’t be proven to be the same.

CHECK

The CHECK constraint lets you define a custom condition that every row must satisfy. This is where SQLite constraints start to feel genuinely powerful, because you’re not limited to simple presence or uniqueness rules — you can enforce arbitrary logical conditions.

Syntax

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL CHECK (price >= 0),
    stock_quantity INTEGER CHECK (stock_quantity >= 0)
);

INSERT INTO products (name, price, stock_quantity) VALUES ('Widget', -5, 10);
-- Fails: price must be >= 0

More Complex CHECK Conditions

You can reference multiple columns within a single CHECK constraint:

CREATE TABLE bookings (
    id INTEGER PRIMARY KEY,
    check_in TEXT NOT NULL,
    check_out TEXT NOT NULL,
    CHECK (check_out > check_in)
);

This ensures that the checkout date is always after the check-in date — a rule that genuinely can’t be expressed through a simple NOT NULL or UNIQUE constraint, but fits perfectly into CHECK.

You can also validate against a fixed list of allowed values, which is a common substitute for a true ENUM type (which SQLite doesn’t natively support):

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    status TEXT CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled'))
);

This restricts status to exactly one of the four listed values, rejecting anything else — a very handy pattern for enforcing consistent categorical data.

FOREIGN KEY

The FOREIGN KEY constraint enforces referential integrity between two tables, ensuring that a value in one table actually corresponds to an existing value in another table.

Enabling Foreign Key Enforcement

Here’s something extremely important to know: SQLite does not enforce foreign key constraints by default, for backward compatibility reasons. You must explicitly enable it, typically at the start of every database connection:

PRAGMA foreign_keys = ON;

If you forget this step, your FOREIGN KEY constraints will be silently ignored, and SQLite will let you insert values that reference nonexistent rows. This trips up an enormous number of beginners, so I want to emphasize it clearly: always run PRAGMA foreign_keys = ON; at the start of your session if you’re relying on foreign key enforcement.

Syntax

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date TEXT,
    FOREIGN KEY (customer_id) REFERENCES customers (id)
);

With PRAGMA foreign_keys = ON; active, SQLite will now reject any attempt to insert an order with a customer_id that doesn’t exist in the customers table.

INSERT INTO orders (customer_id, order_date) VALUES (999, '2026-08-14');
-- Fails if customer with id 999 doesn't exist

ON DELETE and ON UPDATE Actions

Foreign keys can also specify what should happen when the referenced row is deleted or updated.

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date TEXT,
    FOREIGN KEY (customer_id) REFERENCES customers (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Common options include:

I use ON DELETE CASCADE fairly often for genuinely dependent child data — like order_items that make no sense to exist without their parent order. I use ON DELETE RESTRICT or the default NO ACTION when I want to force a deliberate, explicit decision before allowing a deletion that would orphan related data.

DEFAULT

While not a constraint in the strictest sense (it doesn’t reject invalid data — it just supplies a fallback), DEFAULT is closely related and commonly used alongside real constraints.

Syntax

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    status TEXT DEFAULT 'pending',
    created_at TEXT DEFAULT (datetime('now'))
);

If you insert a row without specifying status or created_at, SQLite automatically fills in 'pending' and the current timestamp, respectively.

INSERT INTO tasks (title) VALUES ('Write article on constraints');
-- status becomes 'pending', created_at becomes the current datetime

Combining Multiple Constraints on One Column

It’s entirely normal, and often necessary, to stack multiple constraints on a single column.

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sku TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    price REAL NOT NULL CHECK (price >= 0),
    category_id INTEGER NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE RESTRICT
);

This single table definition enforces: a required, auto-incrementing primary key; a required and unique SKU; a required product name; a required, non-negative price; and a required, referentially valid category that can’t be deleted while products still reference it. That’s a lot of data integrity guaranteed entirely at the schema level, without a single line of application code.

Adding Constraints to an Existing Table

SQLite’s ALTER TABLE support is more limited than some other database engines. You can add a new column with a DEFAULT and even some simple constraints using ALTER TABLE ... ADD COLUMN, but you generally cannot add a CHECK, UNIQUE, FOREIGN KEY, or change NOT NULL on an existing column directly.

The standard workaround is:

  1. Create a new table with the desired constraints.
  2. Copy the data from the old table into the new one.
  3. Drop the old table.
  4. Rename the new table to the original name.
CREATE TABLE products_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL CHECK (price >= 0)
);

INSERT INTO products_new (id, name, price)
SELECT id, name, price FROM products;

DROP TABLE products;

ALTER TABLE products_new RENAME TO products;

This is a common enough pattern in SQLite migrations that it’s worth knowing by heart.

Common Mistakes I See With Constraints

Forgetting PRAGMA foreign_keys = ON;. This is, without question, the single most common constraint-related mistake in SQLite. Foreign keys silently do nothing unless you explicitly enable enforcement.

Assuming INTEGER PRIMARY KEY always behaves like AUTOINCREMENT. It usually does in practice, but without the explicit AUTOINCREMENT keyword, SQLite can technically reuse rowid values from deleted rows in certain situations.

Skipping NOT NULL on columns that genuinely need it. It’s easy to forget, and the cost of forgetting only shows up much later, once bad data has already accumulated.

Not testing CHECK constraints with edge cases. Especially boundary values like exactly 0, empty strings, or NULL (remember, CHECK constraints are satisfied if the expression evaluates to TRUE or NULL — a NULL result does not cause a CHECK constraint to fail, which surprises people).

Best Practices

  1. Always enable foreign key enforcement at the start of every connection if you’re relying on FOREIGN KEY constraints.
  2. Apply NOT NULL generously to any column that should always be populated.
  3. Use CHECK constraints for business rules that go beyond simple presence or uniqueness, like valid ranges or restricted value sets.
  4. Think carefully about ON DELETE behavior for every foreign key — don’t just accept the default without considering whether cascading deletes make sense for that relationship.
  5. Use composite UNIQUE or PRIMARY KEY constraints when uniqueness genuinely depends on a combination of columns, not just one.
  6. Document your constraints, especially non-obvious CHECK conditions, so future maintainers understand the business rule behind the SQL.

Table-Level vs. Column-Level Constraint Syntax

SQLite lets you define constraints in two different places within a CREATE TABLE statement: inline, right next to the column they apply to (column-level), or separately at the end of the table definition (table-level). Both approaches are valid, and the choice largely comes down to readability and whether the constraint spans multiple columns.

-- Column-level constraint
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    price REAL CHECK (price >= 0)
);

-- Table-level constraint (equivalent for single-column constraints)
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    price REAL,
    CHECK (price >= 0)
);

For constraints that involve a single column, I generally prefer the column-level style, since it keeps the rule visually close to the column it governs. For constraints spanning multiple columns — like a composite UNIQUE, a composite PRIMARY KEY, or a CHECK that compares two columns to each other — table-level syntax is required, since there’s no single column to attach the constraint to.

CREATE TABLE bookings (
    id INTEGER PRIMARY KEY,
    check_in TEXT NOT NULL,
    check_out TEXT NOT NULL,
    room_id INTEGER NOT NULL,
    FOREIGN KEY (room_id) REFERENCES rooms(id),
    CHECK (check_out > check_in),
    UNIQUE (room_id, check_in)
);

Naming Your Constraints

By default, SQLite generates internal names for constraints automatically, but you can — and often should — give them explicit, meaningful names using the CONSTRAINT keyword. This makes error messages far more useful and makes it easier to identify exactly which rule was violated when something goes wrong.

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    price REAL,
    CONSTRAINT chk_price_non_negative CHECK (price >= 0),
    CONSTRAINT uq_sku UNIQUE (sku)
);

When a named constraint is violated, SQLite’s error messages become noticeably more descriptive, which is a genuine time-saver when debugging failed inserts or updates in a larger application, especially one with many overlapping validation rules across several tables.

Deferred Foreign Key Constraints

By default, SQLite checks foreign key constraints immediately, as each statement executes. In some situations — particularly when inserting rows with circular or interdependent references within a single transaction — you may need to defer this checking until the transaction commits, rather than after each individual statement.

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT,
    head_employee_id INTEGER REFERENCES employees(id) DEFERRABLE INITIALLY DEFERRED
);

With DEFERRABLE INITIALLY DEFERRED, SQLite postpones checking this particular foreign key until the entire transaction is about to commit, rather than immediately after the INSERT or UPDATE statement that touches it. This is useful for genuinely circular relationships — for example, a departments table referencing an employee as its head, while that same employee’s row references the department they belong to — where inserting either row first would otherwise violate the foreign key temporarily.

Frequently Asked Questions

Can I have more than one PRIMARY KEY on a table?

No, a table can only have one PRIMARY KEY, though that primary key can span multiple columns (a composite key), which effectively lets it cover more than one piece of identifying data.

Does SQLite support ENUM types for constraints?

Not natively. The standard workaround is a CHECK constraint restricting a TEXT column to a fixed list of allowed values, as shown earlier in this article, since SQLite’s type system doesn’t include a dedicated enumerated type.

What happens if I violate a constraint inside a transaction?

The specific statement that violated the constraint fails and is rolled back, but by default (ABORT conflict resolution), previously successful statements within the same transaction remain intact unless you explicitly roll back the whole transaction yourself, or the constraint was declared with different conflict resolution behavior.

Can I temporarily disable constraint checking?

Foreign key checks specifically can be turned off with PRAGMA foreign_keys = OFF;, which is sometimes useful during complex data migrations. Other constraint types, like CHECK, NOT NULL, and UNIQUE, cannot be selectively disabled in the same way — they’re enforced unconditionally whenever an insert or update is attempted.

Wrapping Up

Constraints are what turn a loose collection of tables into a genuinely trustworthy database. PRIMARY KEY gives every row a stable identity, NOT NULL guarantees required fields are never empty, UNIQUE prevents duplicate values, CHECK enforces custom business rules, and FOREIGN KEY keeps relationships between tables honest and consistent — as long as you remember to turn on enforcement.

My strongest recommendation: design your constraints at the same time you design your schema, not as an afterthought. It’s far easier to build data integrity in from the start than to try to clean up years of inconsistent data later.

Exit mobile version