SQLite and Foreign Keys to Improve Data Integrity

Data integrity is one of the most important aspects of database design. A well-designed database prevents inconsistent or invalid data from being stored, making applications more reliable and easier to maintain. SQLite provides several mechanisms to help maintain data integrity, including support for transactions and foreign key constraints.

Database Integrity and the ACID Principles

Database systems are designed around the ACID principles, which define the characteristics of reliable transactions.

• Atomicity ensures that a transaction is treated as a single unit of work. Either all operations succeed, or none of them are applied.
• Consistency guarantees that every transaction leaves the database in a valid state, preserving all defined rules and constraints.
• Isolation ensures that concurrent transactions do not interfere with each other, preventing one transaction from seeing the incomplete results of another.
• Durability guarantees that once a transaction has been committed, its changes are permanently stored, even in the event of a system failure.

Together, these four principles help protect the integrity and reliability of your data. While transactions enforce the ACID properties during database operations, constraints ensure that the data itself remains valid.

Database Constraints

Database constraints are rules that define which values are allowed in a table. They are enforced by the database engine itself, ensuring that invalid data cannot be inserted or updated, regardless of which application accesses the database.

The most common constraint types are:

• Not NULL - A specific column is required to contain a value.
• Primary key - Identifies each row uniquely. A primary key cannot contain duplicate values or NULL values, ensuring that every record has a unique identity.
• Unique - Ensures that the values in one or more columns are unique across the table. Unlike a primary key, a table can contain multiple unique constraints.
• Check - Validates that a value satisfies a specified condition. For example, a check constraint can require that a quantity is greater than zero or that a discount percentage falls between 0 and 100.
• Foreign key - Maintains referential integrity by ensuring that values in one table correspond to existing rows in another table.

By enforcing these rules at the database level, constraints protect the integrity of your data even if multiple applications or users access the same database. Rather than relying solely on application code to perform validation, the database itself becomes the final authority on what constitutes valid data.

The following examples demonstrate how these constraint types work together in a simple customer and order database.

Customers Table

The customers table uses several not null constraints, a primary key to uniquely identify each customer, a unique constraint to prevent duplicate email addresses, and a check constraint to ensure that only valid customer statuses can be stored.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    first_name   TEXT NOT NULL,
    last_name    TEXT NOT NULL,
    email        TEXT NOT NULL UNIQUE,
    phone_number TEXT,
    notes        TEXT,
    status       TEXT NOT NULL
        CHECK (status IN ('ACTIVE', 'INACTIVE')),
    created_at   TEXT NOT NULL
);

Inserting a record with a value for status that doesn't conform to the constraint, will cause the database to reject the command.

INSERT INTO customers_test
( customer_id, first_name, last_name, email, phone_number, notes, status, created_at )
VALUES
( 0, 'John', 'Smith', 'john.smith@example.com', '', '', 'unknown' /* invalid status value */, current_date )

>ERROR: CHECK constraint failed: status IN ('ACTIVE', 'INACTIVE')

Orders Table

The orders table also contains several constraints. Each order has a unique order number, the order total must be greater than zero, and the customer_id column is defined as a foreign key referencing the customers table.

CREATE TABLE orders (
    order_id      INTEGER PRIMARY KEY,
    order_number  TEXT NOT NULL UNIQUE,
    customer_id   INTEGER NOT NULL,
    order_date    TEXT NOT NULL,
    total_amount  NUMERIC NOT NULL
        CHECK (total_amount > 0),
    notes         TEXT,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

Together, these two tables demonstrate all five major constraint types. Some columns require a value, some don't, the primary key uniquely identifies each row, the unique constraint prevents duplicate values, the check constraint enforces business rules, and the foreign key guarantees that every order references an existing customer.

What Are Foreign Key Constraints?

Of all the available constraint types, foreign keys are unique because they enforce relationships between tables rather than rules within a single table.

A foreign key constraint defines a relationship between two tables. It ensures that a value stored in one table refers to an existing row in another table.

In the previous example, every order belongs to a customer. The foreign key constraint guarantees that every customer_id stored in the orders table already exists in the customers table. Without this constraint, an application could accidentally create orders for customers that do not exist.

Foreign key constraints help prevent common data integrity problems, such as:

• Orders referencing customers that do not exist.
• Child records remaining after a parent record has been deleted (unless explicitly allowed).
• Accidental insertion of invalid reference values.

In addition to validating inserts, foreign key constraints can also define what happens when parent rows are updated or deleted. SQLite supports several referential actions, including:

• RESTRICT - Prevents the parent row from being updated or deleted while related child rows exist.
• CASCADE - Automatically propagates updates or deletes to the related child rows.
• SET NULL - Sets the foreign key column in the child table to NULL.
• SET DEFAULT - Sets the foreign key column to its default value.

SQLite supports foreign key constraints, but enforcement is disabled by default for each database connection. To enable constraint checking, execute:

PRAGMA foreign_keys = ON;

Once enabled, SQLite automatically validates inserts, updates, and deletes against all defined foreign key relationships.

In the example above, trying to insert a new orders record for a customer that doesn't exist, will result in an errror, the database rejects the data.

INSERT INTO orders
( order_id, order_number, customer_id, order_date, total_amount, notes )
VALUES
( 28, 24, -1 /* invalid customer_id value */, current_date, 120.12, '' )

>ERROR: FOREIGN KEY constraint failed

Adding a Foreign Key Constraint to an Existing SQLite Table

Unlike some other database systems, SQLite does not allow an existing table to be modified with ALTER TABLE to add a foreign key constraint.

Instead, the table must be recreated with the new definition. This migration typically consists of the following steps:

1. Execute PRAGMA foreign_keys = FALSE.
2. Create a temporary table with the same structure as the original table, including the new foreign key constraint.
3. Copy the existing data into the temporary table.
4. Drop any views that depend on the original table.
5. Drop the original table.
6. Rename the temporary table to the original table name.
7. Execute PRAGMA foreign_keys = TRUE.
8. Recreate any indexes that existed on the original table.
9. Recreate the views that depend on the table.

Although the process is straightforward, it is also repetitive and error-prone, particularly for tables with multiple indexes, triggers, or dependent views. Performing these steps manually increases the risk of overlooking part of the database schema.

Here's an example in case we want to add a foreign key constraint to an orders table pointing towards a customers table:

/* disable foreign keys, so the table can be dropped even when there's other tables pointing towards this table */
PRAGMA foreign_keys = false;

/* create a temporary table that includes the new foreign key */
CREATE TABLE orders_92200375
(
    orderid                Integer NOT NULL,
    custid                 Integer NOT NULL,
    order_date             DateTime NOT NULL,
    expected_delivery_date Date,
    customer_notes         BLOB, 
    CONSTRAINT PK_orders PRIMARY KEY (orderid), 
    CONSTRAINT FK_orders_customers FOREIGN KEY (custid) REFERENCES customers (custid) ON DELETE NO ACTION ON UPDATE CASCADE
);

/* transfer existing data to the temporary table */
INSERT INTO orders_92200375 (orderid, custid, order_date, expected_delivery_date, customer_notes)
SELECT orderid, custid, order_date, expected_delivery_date, customer_notes FROM orders;

/* drop the views that use the table */
DROP VIEW IF EXISTS orders_from_customers;

/* drop the original table */
DROP TABLE IF EXISTS orders;

/* rename the temporary table into the original */
ALTER TABLE orders_92200375 RENAME TO orders;

/* re-enable foreign keys */
PRAGMA foreign_keys = true;

/* re-create objects, in this case, a single view */
CREATE VIEW orders_from_customers
(
 orderid, order_date, expected_delivery_date, customer_notes, custid, 
 cust_name, cust_email, address, postcode, countrycode, phonenumber, regdate
) AS
SELECT
    o.orderid, o.order_date, o.expected_delivery_date, o.customer_notes, c.custid,
    c.cust_name, c.cust_email, c.address, c.postcode, c.countrycode, c.phonenumber, c.regdate
FROM orders o
JOIN customers c ON o.custid = c.custid;

Let Database Workbench Do the Work

Fortunately, you don't have to perform these migration steps manually.

The multi-database tool Database Workbench automates the entire process. Simply open the Table Editor, add the desired foreign key constraint, and save your changes.

Database Workbench analyzes the required schema changes and automatically performs all necessary migration steps. It creates the replacement table, copies the existing data, rebuilds indexes, recreates dependent views, and safely replaces the original table. This allows you to improve database integrity without having to write complex migration scripts yourself.

We have a video with a demonstration available on our Youtube channel.

One Tool for Multiple Database Systems

Although this article focuses on SQLite, Database Workbench is designed for developers who work with multiple database platforms. Instead of learning and maintaining different administration tools for each database engine, you can use a single, consistent interface across a wide range of database systems.

Database Workbench supports the following open source databases:

• SQLite
• MySQL
• MariaDB
• PostgreSQL
• Firebird

It also supports several popular commercial database systems:

• Microsoft SQL Server
• Oracle Database
• InterBase
• NexusDB

This broad database support makes Database Workbench an ideal choice for database developers and administrators working in heterogeneous environments. Whether you develop applications for multiple database engines or maintain databases for different customers, Database Workbench provides a familiar and productive experience across all supported platforms.

Conclusion

Database integrity begins with a solid schema design. Transactions provide the ACID guarantees that make database operations reliable, while constraints ensure that only valid data can be stored. Among these constraints, foreign keys play a critical role by preserving the relationships between tables and preventing inconsistent or orphaned data.

Although SQLite requires an existing table to be recreated when adding a foreign key constraint, Database Workbench makes this process effortless. Simply open the Table Editor, add the constraint, and let Database Workbench perform the required migration automatically. Combined with its extensive support for both open source and commercial database systems, it provides a powerful, unified environment for professional database development.