Constraints

In the last lesson, you created a users table and sprinkled in a few rules like NOT NULL and DEFAULT without really digging into them. Those rules are called constraints. They tell SQLite to reject data that does not meet certain conditions, and they are a quiet but powerful part of schema design.

Why does this matter? Because without constraints, nothing stops bad data from getting into your database. An empty email. A duplicate username. A negative age. If your table allows it, someone (or some bug in your code) will eventually insert it, and cleaning that mess up later is much harder than preventing it in the first place. Constraints are your first line of defense.

Let’s walk through each one.

NOT NULL

By default, any column in SQLite can hold NULL, which means “no value.” Adding NOT NULL to a column definition means SQLite will reject any insert or update that tries to put NULL in that column.

name TEXT NOT NULL

What do you think happens if you try to insert a row without providing a name?

INSERT INTO users (email) VALUES ('[email protected]');

SQLite returns an error: NOT NULL constraint failed: users.name. The row is not inserted. Your data stays clean.

Use NOT NULL on any column where a missing value would not make sense. A user without a name? That is probably a bug. A user without a bio? That might be fine, so you would leave bio as nullable (meaning it can be NULL).

The question to ask yourself is: “Does every row absolutely need a value in this column?” If the answer is yes, add NOT NULL.

UNIQUE

UNIQUE means no two rows can have the same value in this column.

email TEXT NOT NULL UNIQUE

What happens if you try to insert two users with the same email?

INSERT INTO users (email, name) VALUES ('[email protected]', 'Alice');
INSERT INTO users (email, name) VALUES ('[email protected]', 'Alice Again');

The second insert fails with UNIQUE constraint failed: users.email. The first row is safe, and the duplicate is rejected.

This is how you prevent duplicate data. You can also add UNIQUE across multiple columns. Imagine you are building a “bookmark” feature where a user can bookmark a book. You want to make sure a user can only bookmark each book once:

CREATE TABLE bookmarks (
  user_id INTEGER NOT NULL,
  book_id INTEGER NOT NULL,
  UNIQUE(user_id, book_id)
);

That last line is a table-level constraint. Instead of being attached to a single column, it applies across a combination of columns. The pair (user_id, book_id) must be unique. But user_id alone can repeat (a user can bookmark many books), and book_id alone can repeat (a book can be bookmarked by many users). It is only the combination that must be unique. This is exactly how production apps prevent duplicate bookmarks, likes, votes, and follow relationships.

DEFAULT

DEFAULT provides a value when the insert does not specify one.

created_at TEXT NOT NULL DEFAULT (datetime('now'))

Here, if you omit created_at from your insert, SQLite evaluates datetime('now') at insert time and fills in the current timestamp. You get a value automatically without having to supply one.

Defaults can be simple literal values:

is_active INTEGER NOT NULL DEFAULT 1

Or expressions wrapped in parentheses:

uuid TEXT NOT NULL DEFAULT (hex(randomblob(16)))

That second example generates a random hex string every time a row is inserted. The parentheses tell SQLite to evaluate the expression at insert time rather than treating it as a static value.

PRIMARY KEY

We touched on this in the previous lesson, but let’s reinforce it. PRIMARY KEY means the column uniquely identifies each row. It implies both UNIQUE and NOT NULL, with one SQLite-specific exception: an INTEGER PRIMARY KEY column technically allows NULL on insert, which SQLite then replaces with the next available rowid.

A table can only have one primary key.

CHECK

CHECK lets you write a custom condition that must be true for every row. It is like a mini validation rule built right into the schema.

age INTEGER CHECK(age >= 0)

What happens if someone tries to insert a negative age? SQLite rejects it. You cannot have an age of -5.

CHECK is useful for enforcing business rules directly in the database:

status TEXT NOT NULL CHECK(status IN ('draft', 'published', 'archived'))

This ensures status can only ever be one of those three values. If your application code accidentally tries to set it to 'deleted' or an empty string, the database catches it.

Combining constraints

Constraints stack. A single column can have several:

email TEXT NOT NULL UNIQUE CHECK(length(email) > 0)

This means three things at once: the email column must always have a value (NOT NULL), that value must be unique across all rows (UNIQUE), and it cannot be an empty string (CHECK). Three rules, all enforced at the database level.

Why is the CHECK useful here if we already have NOT NULL? Because NOT NULL only prevents NULL. An empty string '' is not NULL. It is a valid text value that happens to have zero characters. The CHECK constraint catches that case too.

[!TIP] Constraints run inside the database, so they protect you regardless of which application, script, or person is inserting the data. When in doubt, add the constraint. It is much easier to relax a rule later than to clean up bad data that got in because the rule was missing.

Exercises

Exercise 1: Create a products table with columns id, name, price, and stock. Add a NOT NULL constraint on name and price. Add a CHECK constraint that ensures price > 0 and stock >= 0. Try inserting a product with a negative price and verify that SQLite rejects it.

Exercise 2: Add a UNIQUE constraint on name in your products table. Insert two products with the same name and observe the error.

Exercise 3: Try inserting a user with an empty string for email: INSERT INTO users (email, name) VALUES ('', 'Ghost');. Does it succeed? If your table does not have CHECK(length(email) > 0), it will. Add that constraint and try again.

Our table now has structure and rules. Bad data has a much harder time sneaking in. There is still one foundational concept we glossed over though. We keep talking about the id column as the thing that uniquely identifies each row, but we have not really picked it apart. In the next lesson, we will look at primary keys in more detail and decide when to use integers vs. UUIDs.