foreign key不难理解嘛

SQLite Foreign Key Support 讲得挺清楚的啊。

更短的例子：

CREATE TABLE person(name TEXT PRIMARY KEY, addr TEXT);
INSERT INTO person VALUES ('tom', 'somewhere');

CREATE TABLE team1(member TEXT, FOREIGN KEY(member) REFERENCES person(name));
INSERT INTO team1 VALUES ('tom');

-- 有没有index只影响查询速度
CREATE INDEX idx ON team1 (member) ;
SELECT * FROM team1 WHERE member = 'tom';
DROP INDEX idx  /* 注释 */ ;
SELECT * FROM team1 WHERE member = 'tom';

PRAGMA foreign_keys = ON;
/* Error: near line 13: FOREIGN KEY constraint failed. 
   要能成功删掉，tom就不是人了。
*/
DELETE FROM person WHERE name = 'tom';

$ rm t.db; sqlite3 t.db <t.sql

team1.member可以同时是primary key和foreign key. 设为primary key则数据库自动为它创建index. primary key和index一起上也无妨。

Difference between an Index and a Primary Key

SQLite Create and Drop Index - w3resource

想更理论？

Foreign key - Detailed Pedia | Primary key - Detailed Pedia