SQLite Foreign Keys: A Beginner’s Guide

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 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:

1. 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.
2. 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 ALTER TABLE.

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 customer_id.

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 first_name and last_name:

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 NO ACTION
• 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 ALTER TABLE.

You must instead:

1. Disable foreign key enforcement
2. Rename the table
3. Recreate the table without the foreign key
4. Copy the data into the new table
5. Re-enable foreign key enforcement

For example:

-- 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.

Final Words

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 TABLE in SQLite
• You can configure “actions” like ON DELETE CASCADE to 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