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
- 不是primary key可以当foreign key; 可以同时是primary和foreign key
- 设为primary key则数据库自动为它创建index
- 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?