Can a UNIQUE column contain multiple NULL values in SQLite?

Yes. SQLite treats NULL values as distinct for uniqueness checks, so multiple NULLs are allowed in a UNIQUE column.

SQLite Constraints – PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL | DbSchema

Q: Why is my foreign key not enforced in SQLite?

Most often because PRAGMA foreign_keys = ON was not enabled on that connection. Foreign key enforcement must be turned on per connection in SQLite.

Q: What is the difference between PRIMARY KEY and UNIQUE in SQLite?

Both prevent duplicates, but PRIMARY KEY identifies the main row identity for the table, while UNIQUE is usually for additional business keys such as email or external code.

Sqlite • 04-Jun-2023

by Malik Umair Sajjad

SQLite constraints and data integrity rules >

What SQLite constraints do
SQLite constraint types
Create tables with constraints in sqlite3
Foreign keys and PRAGMA foreign_keys
Apply constraints from Python
Manage constraints in DbSchema
Edge cases and limits
Performance and design tips
FAQ
Conclusion
References

SQLite constraints protect data quality by preventing invalid rows from being inserted or updated. Without them, application code has to enforce every rule on its own, which is risky and hard to maintain.

This guide explains the core SQLite constraint types, shows practical sqlite3 and Python examples, covers foreign key caveats, and shows how DbSchema helps manage constraints visually.

What SQLite constraints do

Constraints define what values a table can accept.

They are used to:

make required columns truly required
prevent duplicates
validate ranges and allowed values
preserve parent-child relationships
set safe defaults for missing values

If you are still designing the table structure, review SQLite CREATE TABLE first.

SQLite constraint types

Constraint	Purpose	Example
`PRIMARY KEY`	uniquely identifies each row	`customer_id INTEGER PRIMARY KEY`
`FOREIGN KEY`	preserves references between tables	`FOREIGN KEY(customer_id) REFERENCES customers(customer_id)`
`UNIQUE`	disallows duplicate values	`email TEXT UNIQUE`
`NOT NULL`	requires a value	`name TEXT NOT NULL`
`DEFAULT`	fills in an omitted value	`status TEXT DEFAULT 'new'`
`CHECK`	enforces custom rules	`CHECK(total_cents >= 0)`

Sample schema using several constraints together:

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    email       TEXT UNIQUE,
    status      TEXT NOT NULL DEFAULT 'active'
                 CHECK (status IN ('active', 'disabled'))
);

CREATE TABLE orders (
    order_id     INTEGER PRIMARY KEY,
    customer_id  INTEGER NOT NULL,
    total_cents  INTEGER NOT NULL CHECK (total_cents >= 0),
    created_at   TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
);

Create tables with constraints in sqlite3

Open the shell:

sqlite3 shop.db

Then create the schema:

PRAGMA foreign_keys = ON;

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    email       TEXT UNIQUE,
    status      TEXT NOT NULL DEFAULT 'active'
                 CHECK (status IN ('active', 'disabled'))
);

CREATE TABLE orders (
    order_id     INTEGER PRIMARY KEY,
    customer_id  INTEGER NOT NULL,
    total_cents  INTEGER NOT NULL CHECK (total_cents >= 0),
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
);

Test constraint violations

INSERT INTO customers (customer_id, name, email)
VALUES (1, 'Bob', '[email protected]');

INSERT INTO customers (customer_id, name, email)
VALUES (2, 'Alice', '[email protected]');

The second insert fails because of the UNIQUE constraint on email.

Another example:

INSERT INTO orders (order_id, customer_id, total_cents)
VALUES (1, 1, -50);

This fails because of CHECK (total_cents >= 0).

Foreign keys and PRAGMA foreign_keys

Foreign keys are one of the most important SQLite caveats.

SQLite supports foreign key constraints, but they are enforced only when the connection has:

PRAGMA foreign_keys = ON;

Check the current setting:

PRAGMA foreign_keys;

If your app opens multiple connections, make sure each connection enables foreign key enforcement where needed.

This matters directly for:

delete cascades
preventing orphan rows
keeping joins trustworthy

Related follow-up pages:

Apply constraints from Python

Python's sqlite3 module works well for repeatable schema creation and migration scripts.

import sqlite3

schema_sql = """
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS customers (
    customer_id INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    email       TEXT UNIQUE
);

CREATE TABLE IF NOT EXISTS orders (
    order_id     INTEGER PRIMARY KEY,
    customer_id  INTEGER NOT NULL,
    total_cents  INTEGER NOT NULL CHECK (total_cents >= 0),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
"""

try:
    with sqlite3.connect("shop.db") as connection:
        connection.executescript(schema_sql)
        connection.execute(
            "INSERT INTO orders(order_id, customer_id, total_cents) VALUES (?, ?, ?)",
            (1, 999, 2500)
        )
except sqlite3.IntegrityError as error:
    print("Constraint violation:", error)

This is a practical pattern for tests and setup scripts because it gives you a clear exception when the data violates a rule.

Manage constraints in DbSchema

DbSchema helps when you want to review constraint logic without digging through raw DDL every time.

Typical workflow:

Open the SQLite database or model in DbSchema.
Double-click a table in the diagram.
Configure column rules such as NOT NULL, DEFAULT, and UNIQUE.
Add foreign keys and check constraints visually.
Generate or synchronize the SQL changes.
Use the SQL Editor to test inserts and verify constraint behavior.

Useful docs:

DbSchema is especially helpful when multiple people need to understand how the rules fit together across tables and relationships.

Edge cases and limits

UNIQUE and NULL

SQLite allows multiple NULL values in a UNIQUE column because NULL is treated as unknown, not equal to another NULL.

CHECK constraints

CHECK is great for ranges and allowed-value lists, but it is not a full replacement for application logic. Keep the expressions simple and easy to understand.