A-A+
A SQLite foreign key example
First, we define two database tables that don't have any foreign keys:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- -- 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, we define a SQLite table that has two foreign keys, one that relates our new orders table back to the customers table, and a second foreign key that relates the orders table back to the salespeople table:
|
1 2 3 4 5 6 7 8 9 10 |
-- -- orders -- CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER, salesperson_id INTEGER, <strong>FOREIGN KEY</strong>(customer_id) REFERENCES customers(id), <strong>FOREIGN KEY</strong>(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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- -- 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.
