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 /* PRIMRAY KEY*/, 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 -f t.db; sqlite3 t.db <t.sql

1. 不是primary key可以当foreign key; 可以同时是primary和foreign key
2. 设为primary key则数据库自动为它创建index
3. primary key和index一起上无妨(还是一个索引)

Difference between an Index and a Primary Key | SQLite Create and Drop Index

Foreign key - Detailed Pedia | Primary key - Detailed Pedia

把name换成ptr，addr换成内存地址：What is a dangling pointer?