SQLite外键全面解析

一、什么是SQLite外键

SQLite外键是指将两个表格关联起来的键，用于确保数据的一致性和完整性，是数据库设计中最关键的一环。

在SQLite中，外键指一个表格中的一列或者多列值需要与另一个表格中的某一列值保持一致，以此来确保数据的一致性。

二、定义外键的方法

在定义表的时候，需要在需要参考其他表格的列名后面加上“REFERENCES”关键字，接着是被参考的表格名和被参考的列名。具体语法如下：

CREATE TABLE table_name (
  column1 datatype CONSTRAINT constraint_name REFERENCES referenced_table (referenced_column),
  column2 datatype,
  column3 datatype,
  .....
);

其中，“column1”是需要定义为外键的列名，“datatype”是数据类型，“constraint_name”是定义的外键约束名字，“referenced_table”是被参考的表格名，“referenced_column”是被参考的列名。

三、外键的约束种类

1. CASCADE

若主表中删除了一条记录，那么从表中与此记录关联的数据行也将被自动删除。

CREATE TABLE students (
	id INTEGER PRIMARY KEY,
	name TEXT,
	age INTEGER);

CREATE TABLE courses (
	id INTEGER PRIMARY KEY,
	course_name TEXT,
	student_id INTEGER,
	FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE CASCADE);

2. RESTRICT

若主表中删除了一条记录，那么从表中如果有要与此记录关联的数据行，删除操作将被拒绝。

CREATE TABLE students (
	id INTEGER PRIMARY KEY,
	name TEXT,
	age INTEGER);

CREATE TABLE courses (
	id INTEGER PRIMARY KEY,
	course_name TEXT,
	student_id INTEGER,
	FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE RESTRICT);

3. NO ACTION

若主表中删除了一条记录，那么从表中如果有要与此记录关联的数据行，将会限制此操作，但是不会有任何动作被执行。

CREATE TABLE students (
	id INTEGER PRIMARY KEY,
	name TEXT,
	age INTEGER);

CREATE TABLE courses (
	id INTEGER PRIMARY KEY,
	course_name TEXT,
	student_id INTEGER,
	FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE NO ACTION);

4. SET NULL

若主表中删除了一条记录，那么从表中与此记录关联的数据行的外键值将被设置为 NULL。

CREATE TABLE students (
	id INTEGER PRIMARY KEY,
	name TEXT,
	age INTEGER);

CREATE TABLE courses (
	id INTEGER PRIMARY KEY,
	course_name TEXT,
	student_id INTEGER,
	FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE SET NULL);

5. SET DEFAULT

若主表中删除了一条记录，那么从表中与此记录关联的数据行的外键值将被设置为默认值。

CREATE TABLE students (
	id INTEGER PRIMARY KEY,
	name TEXT,
	age INTEGER);

CREATE TABLE courses (
	id INTEGER PRIMARY KEY,
	course_name TEXT,
	student_id INTEGER DEFAULT 1,
	FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE SET DEFAULT);

四、外键的查询方法

可以使用“PRAGMA foreign_key_list(table_name)”来查看指定表的外键约束信息。

PRAGMA foreign_key_list(courses);

结果如下：

0|courses|student_id|students|id|RESTRICT|NO ACTION|NONE

五、外键的局限性

SQLite对外键的支持存在以下局限性：

• 不能定义跨库的外键；
• 不能定义触发器或者存储过程；
• 不能复合外键；
• 在SQLite默认情况下是不支持外键的，需要手动开启外键约束。

六、手动开启SQLite外键约束

在创建数据库连接时，需要手动开启外键约束。具体实现方法是调用sqlite3_exec函数执行PRAGMA语句，如下所示：

sqlite3 *db;
sqlite3_open("test.db", &db);
char *zErrMsg = 0;
sqlite3_exec(db, "PRAGMA foreign_keys = ON;", 0, 0, &zErrMsg);

七、总结

SQLite外键用于确保数据的一致性和完整性，在数据库设计中起着至关重要的作用。SQLite支持多种外键约束，包括CASCADE、RESTRICT、NO ACTION、SET NULL和SET DEFAULT，并且可以使用PRAGMA语句查询指定表的外键信息。但是，SQLite对外键的支持存在一些局限性，需要开发人员在使用时注意。