SQLite foreign key examples

SQLite foreign keys FAQ: Can you show me how to define foreign keys in a SQLite database table design?

Solution

The SQLite database does support foreign keys, and its foreign key syntax is similar to other databases. Here’s a quick SQLite foreign key example.

A SQLite foreign key example

To show how this works, first define two database tables that don’t have any foreign keys:

--
-- salespeople
--
CREATE TABLE salespeople (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    commission_rate REAL NOT NULL
);

--
-- customers
--
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    company_name TEXT NOT NULL,
    street_address TEXT NOT NULL,
    city TEXT NOT NULL,
    state TEXT NOT NULL,
    zip TEXT NOT NULL
);

Next, define a SQLite table that has two foreign keys, one that relates a new orders table back to the customers table, and a second foreign key that relates the orders table back to the salespeople table:

--
-- orders
--
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    salesperson_id INTEGER,
    FOREIGN KEY(customer_id) REFERENCES customers(id),
    FOREIGN KEY(salesperson_id) REFERENCES salespeople(id)
);

As you can see, the SQLite foreign key syntax is very similar to other databases.

Sample SQLite foreign key data

If you'd like to test this SQLite foreign key example in your own SQLite database, here's some sample data for each of these tables:

--
-- salespeople sample data
--
INSERT INTO salespeople VALUES (null, 'Fred', 'Flinstone', 10.0);
INSERT INTO salespeople VALUES (null, 'Barney', 'Rubble', 10.0);

--
-- customers sample data
--
INSERT INTO customers VALUES (null, 'ACME, INC.', '101 Main Street', 'Anchorage', 'AK', '99501');
INSERT INTO customers VALUES (null, 'FOOBAR', '200 Foo Way', 'Louisville', 'KY', '40207');

--
-- orders sample data
--
INSERT INTO orders VALUES (null, 1, 1);
INSERT INTO orders VALUES (null, 2, 2);

I just tested these SQLite foreign key examples on my system, using SQLite version 3.4.0, and they all work fine.