Have you ever wanted to link two tables in an SQLite database together so that the data remains consistent? That’s exactly what foreign keys allow you to do!
Foreign keys are a crucial component of relational database design. They allow you to establish connections between database tables, ensuring data integrity across related tables.
In this comprehensive guide, you’ll learn:
- What foreign keys are and why they’re important
- How to create foreign keys in SQLite
- Examples of foreign keys in action
- Advanced features like actions and deferred constraints
- How to drop foreign keys
Plus plenty of examples and sample code to help cement your understanding. Let’s dive in!
What's in this article
What Are Foreign Keys?
A foreign key is a column or set of columns in one table that references the primary key of another table. This establishes a link between the two tables, allowing you to maintain referential integrity across your database schema.
Here are some key facts about foreign keys:
- They enforce a parent-child relationship between tables. The table containing the foreign key is the child table, and the referenced table is the parent.
- Values in the foreign key column must match values in the parent table’s primary key column. This maintains data consistency.
- If a record is deleted from the parent table, any records in the child table with foreign keys pointing to that parent record will be deleted as well (by default – more on this later).
- NULL values are allowed in the foreign key column. This means a corresponding parent record is optional for those child records.
So, foreign keys allow you to model relationships like:
- A customer can have many orders, but an order belongs to one customer
- A post can have many comments, but a comment belongs to one post
Without foreign keys, there would be nothing stopping you from inserting orders without a valid customer ID or comments without a valid post ID. Foreign keys add that validation and consistency.
Why Use Foreign Keys?
There are two main reasons to use foreign keys in your SQLite databases:
- Enforce data consistency – As mentioned, foreign keys prevent invalid data from being inserted. All foreign key values must correspond to an existing record in the parent table.
- Cascade deletes – When you delete a record from the parent table, you can configure the foreign key relationship to automatically delete any related records from the child table.
For example, if you deleted a customer, you’d probably want to delete that customer’s orders too. Foreign keys allow you to model that behavior.
You can accomplish some of this programmatically without foreign keys. But it’s much easier to let the database enforce these relationships for you.
Now let’s look at how to actually create foreign keys in SQLite.
Creating Foreign Keys in SQLite
Foreign key support was added to SQLite in version 3.6.19. So you’ll need at least that version to follow along with these examples.
You can only define foreign keys using the
CREATE TABLE statement in SQLite. You can’t add them to existing tables with
Here is the basic syntax:
CREATE TABLE child_table ( child_key INTEGER, CONSTRAINT fk_name FOREIGN KEY (child_key) REFERENCES parent_table(parent_key) );
Let’s create a simple example with customers and orders:
-- Parent table CREATE TABLE customers ( customer_id INTEGER PRIMARY KEY, name TEXT ); -- Child table CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, customer_id INTEGER, CONSTRAINT fk_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );
Here we created a parent
customers table with a primary key column
Then we created a child
orders table that includes a
customer_id foreign key column that references the parent
customer_id primary key.
This ensures every order record must be linked to a valid customer record. Pretty simple!
Now let’s walk through a more detailed example.
Foreign Key Example – Books and Authors
Let’s model a simple database of books and authors.
Each book will be written by one author. And each author can write multiple books. This is a classic one-to-many database relationship.
Here is the schema:
-- Parent table CREATE TABLE authors ( author_id INTEGER PRIMARY KEY, name TEXT ); -- Child table CREATE TABLE books ( book_id INTEGER PRIMARY KEY, author_id INTEGER, title TEXT, CONSTRAINT fk_authors FOREIGN KEY (author_id) REFERENCES authors(author_id) );
First, we create the parent
authors table with the
author_id primary key.
Then we create the child
books table, with an
author_id foreign key that references the
author_id in the parent table.
Let’s insert some sample data:
-- Insert authors INSERT INTO authors (name) VALUES ('Jane Austen'), ('Charles Dickens'), ('Mark Twain'); -- Insert books INSERT INTO books (author_id, title) VALUES (1, 'Pride and Prejudice'), (1, 'Emma'), (2, 'The Pickwick Papers'), (2, 'Oliver Twist'), (3, 'The Adventures of Tom Sawyer');
Now let’s see the foreign key in action by attempting to insert invalid data:
-- Try inserting book with non-existent author INSERT INTO books (author_id, title) VALUES (10, 'Invalid Book'); -- Fails with: -- SQL error: foreign key constraint failed
It fails because there is no
author_id 10 in our
authors table. The foreign key prevents invalid data from being inserted.
If we try to delete an author that books reference, it will fail as well:
-- Try deleting referenced author DELETE FROM authors WHERE name = 'Charles Dickens'; -- Fails with: -- SQL error: foreign key constraint failed
This ensures we cannot orphan books without an associated author record. As you can see, foreign keys enforce data consistency between related tables!
Composite Foreign Keys in SQLite
The previous examples used a foreign key on a single column. You can also create composite foreign keys that reference multiple columns.
For example, if our
authors table used a composite key on
CREATE TABLE authors ( first_name TEXT, last_name TEXT, PRIMARY KEY (first_name, last_name) );
Our child table would need a composite foreign key as well:
CREATE TABLE books ( book_id INTEGER PRIMARY KEY, author_first_name TEXT, author_last_name TEXT, CONSTRAINT fk_authors FOREIGN KEY (author_first_name, author_last_name) REFERENCES authors(first_name, last_name) );
The number of columns must match the foreign key and parent key.
Composite keys allow you to reference multi-column primary keys, which is useful in some scenarios.
Foreign Key Actions
By default, if you try to delete or update a parent table record that has related records in a child table, it will fail and abort the operation.
However, you can configure different “actions” to take place instead using
ON DELETE and
ON UPDATE clauses:
-- Example ON DELETE action CREATE TABLE books ( CONSTRAINT fk_authors FOREIGN KEY (author_id) REFERENCES authors(author_id) ON DELETE CASCADE );
Some common actions include:
NO ACTION(default) – Causes the delete or update operation to fail
RESTRICT– Same as
CASCADE– Delete or update related child records to match the parent change
SET NULL– Set the child records foreign key columns to NULL when the parent record is deleted or updated
SET DEFAULT– Set the child records foreign key columns to a default value when the parent record is deleted or updated
CASCADE is commonly used, as it propagates deletes/updates from the parent table to the child table. However
SET NULL or
SET DEFAULT are useful if you want the child records to persist without a parent reference.
Deferred Constraints in SQLite
Foreign key constraints are immediate by default in SQLite. This means any operation that violates the foreign key will fail immediately.
However, you can optionally define deferred constraints:
CREATE TABLE books ( CONSTRAINT fk_authors FOREIGN KEY (author_id) REFERENCES authors(author_id) DEFERRABLE INITIALLY DEFERRED );
With deferred constraints, foreign key violations won’t cause an error until the transaction is committed.
This allows the database to temporarily be in an inconsistent state within a transaction.
Deferred constraints can be useful in some advanced scenarios when you need to temporarily violate foreign keys. But use caution as they can allow bad data if misused!
Dropping Foreign Keys in SQLite
Unfortunately, SQLite does not support dropping foreign key constraints with
You must instead:
- Disable foreign key enforcement
- Rename the table
- Recreate the table without the foreign key
- Copy the data into the new table
- Re-enable foreign key enforcement
-- Disable foreign keys PRAGMA foreign_keys = OFF; -- Rename table ALTER TABLE books RENAME TO books_old; -- Recreate table CREATE TABLE books ( -- Table schema without FK constraint ); -- Copy data INSERT INTO books SELECT * FROM books_old; -- Enable foreign keys PRAGMA foreign_keys = ON;
This process lets you remove foreign keys from existing tables since
ALTER TABLE does not support it directly.
To wrap up:
- Foreign keys enforce referential integrity between related tables
- They ensure child records always reference a valid parent record
- Foreign keys are defined using
CREATE TABLEin SQLite
- You can configure “actions” like
ON DELETE CASCADEto propagate changes from parent to child tables
- Use caution with deferred foreign key constraints
- And unfortunately dropping foreign keys has to be done manually with a table copy process