Skip to main content

SQLite外键:强制表之间的关系

SQLite外键约束支持

自版本3.6.19以来,SQLite一直支持外键约束。SQLite库也必须用SQLiteOMIT_FOREIGN键或SQLiteOMIT触发器编译。

要检查当前版本的SQLite是否支持外键约束,请使用以下命令。

PRAGMA foreign_keys;

该命令返回一个整数值:1:enable,0:disabled。如果该命令不返回任何内容,则表示您的SQLite版本不支持外键约束。

如果使用外键约束支持编译SQLite库,则应用程序可以在运行时使用PRAGMA foreign_keys命令启用或禁用外键约束。

要禁用外键约束,请执行以下操作:

PRAGMA foreign_keys = OFF;

要启用外键约束,请执行以下操作:

PRAGMA foreign_keys = ON;

SQLite外键约束简介

让我们从两个表开始:供应商和供应商组:

CREATE TABLE suppliers (
supplier_id integer PRIMARY KEY,
supplier_name text NOT NULL,
group_id integer NOT NULL
);

CREATE TABLE supplier_groups (
group_id integer PRIMARY KEY,
group_name text NOT NULL
);

假设每个供应商都属于且仅属于一个供应商组。每个供应商组可能有零个或多个供应商。供应商组和供应商表之间的关系是一对多的。换句话说,对于“供应商”表中的每一行,“供应商组”表中都有相应的行。

目前,没有任何方法可以阻止您在“供应商组”表中没有相应行的情况下向“供应商”表中添加行。

此外,您可以删除supplier_groups表中的一行,而无需删除或更新suppliers表中的相应行。这可能会在suppliers表中留下孤立行。

要加强“供应商”和“供应商组”表中的行之间的关系,可以使用外键约束。

要将外键约束添加到suppliers表中,可以按如下方式更改上面CREATE table语句的定义:

DROP TABLE suppliers;

CREATE TABLE suppliers (
supplier_id INTEGER PRIMARY KEY,
supplier_name TEXT NOT NULL,
group_id INTEGER NOT NULL,
FOREIGN KEY (group_id)
REFERENCES supplier_groups (group_id)
);

SQLite Foreign Key Example

supplier_groups表称为父表,即外键引用的表。suppliers表称为子表,它是应用外键约束的表。

supplier_groups表中的group_id列称为parent key,它是外键约束引用的父表中的一列或一组列。通常,父键是父表的主键。

suppliers表中的group_id列称为子键。通常,子键引用父表的主键。

SQLite外键约束示例

首先,在supplier_groups表中插入三行。

INSERT INTO supplier_groups (group_name)
VALUES
('Domestic'),
('Global'),
('One-Time');

SQLite Foreign Key - Supplier Groups

第二,将一个新的供应商插入到供应商表中,该供应商表中包含供应商组。

INSERT INTO suppliers (supplier_name, group_id)
VALUES ('HP', 2);

这句话很好用。

第三,尝试在supplier_groups表中不存在的supplier group中插入一个新供应商。

INSERT INTO suppliers (supplier_name, group_id)
VALUES('ABC Inc.', 4);

SQLite检查了外键约束,拒绝了更改,并发出以下错误消息:

[SQLITE_CONSTRAINT]  Abort due to constraint violation (FOREIGN KEY constraint failed)

SQLite外键约束操作

如果删除supplier_groups表中的一行,会发生什么情况?供应商表中的所有对应行是否也应删除?更新操作也有同样的问题。

要指定删除或更新父项时外键约束的行为方式,请使用“删除时”或“更新时”操作,如下所示:

FOREIGN KEY (foreign_key_columns)
REFERENCES parent_table(parent_key_columns)
ON UPDATE action
ON DELETE action;

SQLite支持以下操作:

实际上,父表中主键的值不会更改,因此更新规则不那么重要。更重要的规则是删除规则,该规则指定删除父项时的操作。

我们将通过以下示例来检查每个操作

设为空

当父键更改、删除或更新时,子表中所有行的对应子键都将设置为NULL。

首先,使用组id外键的SET NULL操作删除并创建表供应商:

DROP TABLE suppliers;

CREATE TABLE suppliers (
supplier_id INTEGER PRIMARY KEY,
supplier_name TEXT NOT NULL,
group_id INTEGER,
FOREIGN KEY (group_id)
REFERENCES supplier_groups (group_id)
ON UPDATE SET NULL
ON DELETE SET NULL
);

其次,在suppliers表中插入一些行:

INSERT INTO suppliers (supplier_name, group_id)
VALUES('XYZ Corp', 3);

INSERT INTO suppliers (supplier_name, group_id)
VALUES('ABC Corp', 3);

第三,从supplier_groups表中删除供应商组id 3:

DELETE FROM supplier_groups 
WHERE group_id = 3;

第四,从供应商表中查询数据。

SELECT * FROM suppliers;

SQLite Foreign Key - SET NULL

供应商表中相应行的group_id列的值设置为NULL。

设置默认值

设置默认值操作将外键的值设置为创建表时在列定义中指定的默认值。

由于group_id列中的值默认为NULL,因此如果从supplier_groups表中删除一行,group_id的值将设置为NULL。

指定默认值后,外键约束生效并进行检查。

限制

限制操作不允许更改或删除父表的父键中的值。

首先,在外键组_id中删除并创建带有限制操作的suppliers表:

DROP TABLE suppliers;

CREATE TABLE suppliers (
supplier_id INTEGER PRIMARY KEY,
supplier_name TEXT NOT NULL,
group_id INTEGER,
FOREIGN KEY (group_id)
REFERENCES supplier_groups (group_id)
ON UPDATE RESTRICT
ON DELETE RESTRICT
);

第二,在表中插入一行,其组id为1。

INSERT INTO suppliers (supplier_name, group_id)
VALUES('XYZ Corp', 1);

第三,从supplier_groups表中删除id为1的供应商组:

DELETE FROM supplier_groups 
WHERE group_id = 1;

SQLite发出了以下错误:

[SQLITE_CONSTRAINT]  Abort due to constraint violation (FOREIGN KEY constraint failed)

要解决此问题,必须首先删除组id为1的suppliers表中的所有行:

DELETE FROM suppliers 
WHERE group_id =1;

然后,您可以从supplier_groups表中删除supplier group 1:

DELETE FROM supplier_groups 
WHERE group_id = 1;

不采取行动

NO ACTION并不意味着绕过外键约束。它具有与限制类似的效果。

大量

在更新或删除父键时,级联操作将更改从父表传播到子表。

首先,将供应商组插入供应商组表:

INSERT INTO supplier_groups (group_name)
VALUES
('Domestic'),
('Global'),
('One-Time');

SQLite Foreign Key - Supplier Groups

其次,在外键组_id中删除并创建带有级联操作的表suppliers:

DROP TABLE suppliers;

CREATE TABLE suppliers (
supplier_id INTEGER PRIMARY KEY,
supplier_name TEXT NOT NULL,
group_id INTEGER,
FOREIGN KEY (group_id)
REFERENCES supplier_groups (group_id)
ON UPDATE CASCADE
ON DELETE CASCADE
);

第三,在“供应商”表中插入一些供应商:

INSERT INTO suppliers (supplier_name, group_id)
VALUES('XYZ Corp', 1);

INSERT INTO suppliers (supplier_name, group_id)
VALUES('ABC Corp', 2);

第四,将国内供应商组的group_id更新为100:

UPDATE supplier_groups
SET group_id = 100
WHERE group_name = 'Domestic';

第五,从供应商表中查询数据:

SELECT * FROM suppliers;

正如您所见,当我们更新suplier_groups表中的group_id时,XYZ Corp表中group_id列中的值从1变为100。这是更新级联操作的结果。

第六,从供应商组表中删除供应商组id 2:

DELETE FROM supplier_groups 
WHERE group_id = 2;

第七,从供应商表中查询数据:

SELECT * FROM suppliers;

当供应商组id 2从供应商组表中删除时,其组id为2的供应商id 2被删除。这是ON DELETE级联操作的效果。

在本教程中,您已经了解了SQLite外键约束,以及如何使用它们来加强相关表之间的关系。