sqlite foreign keys FAQ: Can you show me how to defineforeign keysin a sqlite database table design?

The sqlite database does supportforeign keys,and its foreign key Syntax is similar to other databases. Here's a quick sqlite foreign key example.

A sqlite foreign key example

First,we 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
);

--
-- 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)
);

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);

原文地址：

http://alvinalexander.com/android/sqlite-foreign-keys-example