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對外鍵的支持存在一些局限性，需要開發人員在使用時注意。