SQLite - Constraints

SQLite - Constraints

Constraints are rules applied to table columns to enforce data integrity. They limit the type of data that can be inserted, ensuring the accuracy and reliability of your database. Constraints can be defined at the column level or the table level.

Key constraints in SQLite include:

• NOT NULL: Ensures a column cannot store NULL values.
• UNIQUE: Ensures all values in a column (or a set of columns) are distinct.
• PRIMARY KEY: A combination of NOT NULL and UNIQUE that uniquely identifies each row in a table.
• FOREIGN KEY: Creates a link between two tables, enforcing referential integrity.
• CHECK: Ensures that values in a column satisfy a specific condition.
• DEFAULT: Provides a default value for a column if no value is specified.

PRIMARY KEY Constraint

A primary key is crucial for identifying records. While any unique column can be a primary key, SQLite has special behavior for columns of type INTEGER PRIMARY KEY.

An INTEGER PRIMARY KEY column becomes an alias for the internal rowid. This creates a high-performance, auto-incrementing 64-bit integer key. This is the recommended approach for creating surrogate keys in SQLite.

-- Recommended way to create a table with an auto-incrementing ID
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY, -- Automatically NOT NULL, UNIQUE, and auto-incrementing
    email TEXT NOT NULL UNIQUE,
    created_at TEXT DEFAULT (datetime('now'))
);

The AUTOINCREMENT keyword is optional. It adds a small overhead but guarantees that newly generated keys will always be larger than any key ever used in that table, even for rows that have been deleted. Use it only if you cannot allow rowid values to be reused.

FOREIGN KEY Constraint

Foreign keys are the cornerstone of relational databases. They create a parent-child relationship between tables, ensuring that a value in the child table has a corresponding entry in the parent table.

Note: For compatibility, it is good practice to enable foreign key support for each connection using PRAGMA foreign_keys = ON;.

Example

Let’s model an authors table and a books table. Each book must be associated with a valid author.

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER NOT NULL,
    FOREIGN KEY (author_id) REFERENCES authors (author_id)
        ON DELETE CASCADE -- If an author is deleted, their books are also deleted.
        ON UPDATE CASCADE -- If an author_id changes, update it in the books table.
);

-- This will succeed
INSERT INTO authors (name) VALUES ('J.R.R. Tolkien');
INSERT INTO books (title, author_id) VALUES ('The Hobbit', 1);

-- This will FAIL because author_id 99 does not exist
INSERT INTO books (title, author_id) VALUES ('The Silmarillion', 99);

Other Common Constraints

• UNIQUE: Ideal for columns that must not have duplicate values, like an email address or username.
• CHECK: Useful for business logic, like ensuring a price is positive or a rating is between 1 and 5.
• DEFAULT: Simplifies INSERT statements by automatically populating values, such as a registration timestamp.

Combined Example

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    sku TEXT NOT NULL UNIQUE, -- Stock Keeping Unit must be unique
    name TEXT NOT NULL,
    price REAL NOT NULL CHECK(price > 0), -- Price must be positive
    stock_count INTEGER DEFAULT 0 -- Default stock to 0 if not provided
);

Modifying Constraints

SQLite’s ALTER TABLE command is limited. You cannot drop or modify constraints on an existing column directly. The standard, safe procedure involves recreating the table inside a transaction.

Example: Adding a NOT NULL constraint

Imagine we have a products table and want to make the name column NOT NULL.

BEGIN TRANSACTION;

-- 1. Create a new table with the desired schema
CREATE TABLE products_new (
    product_id INTEGER PRIMARY KEY,
    sku TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL, -- The new constraint is added here
    price REAL NOT NULL CHECK(price > 0),
    stock_count INTEGER DEFAULT 0
);

-- 2. Copy the data from the old table to the new one
INSERT INTO products_new (product_id, sku, name, price, stock_count)
SELECT product_id, sku, name, price, stock_count FROM products;

-- 3. Drop the old table
DROP TABLE products;

-- 4. Rename the new table to the original name
ALTER TABLE products_new RENAME TO products;

COMMIT;