Skip to main content

SQLite检查约束的基本指南

SQLite检查约束简介

SQLite检查约束允许您定义表达式,以便在将值插入列或在列中更新时测试值。

如果值不符合表达式定义的条件,SQLite将发出约束冲突并中止该语句。

检查约束允许您在UNIQUE或NOT NULL之外定义其他数据完整性检查,以适合您的特定应用程序。

SQLite允许您在列级别或表级别定义检查约束。

下面的语句显示了如何在列级别定义检查约束:

CREATE TABLE table_name(
...,
column_name data_type CHECK(expression),
...
);

下面的语句说明了如何在表级别定义检查约束:

CREATE TABLE table_name(
...,
CHECK(expression)
);

在这种语法中,每当将一行插入表中或更新现有行时,都会计算与每个检查约束关联的表达式,并返回一个数值0或1。

如果结果为零,则发生约束冲突。如果结果为非零值或NULL,则表示没有违反约束。

请注意,检查约束的表达式不能包含子查询。

SQLite检查约束示例

让我们举一些使用检查约束的例子。

1) 在列级示例中使用SQLite检查约束

下面的语句将创建一个名为contacts的新表:

CREATE TABLE contacts (
contact_id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email TEXT,
phone TEXT NOT NULL
CHECK (length(phone) >= 10)
);

在contacts表中,phone列有一个检查约束:

CHECK (length(phone) >= 10) 

此检查约束确保phone列中的值必须至少为10个字符。

如果尝试执行以下语句,将出现约束冲突错误:

INSERT INTO contacts(first_name, last_name, phone)
VALUES('John','Doe','408123456');

以下是错误消息:

Result: CHECK constraint failed: contacts

原因是您试图插入的电话号码只有9个字符,而它至少需要10个字符。

由于phone列中的值有13个字符,满足CHECK约束中的表达式,因此以下语句应该有效:

INSERT INTO contacts(first_name, last_name, phone)
VALUES('John','Doe','(408)-123-456');

2) 在表级示例中使用SQLite检查约束

下面的语句将创建一个名为products的新表:

CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name TEXT NOT NULL,
list_price DECIMAL (10, 2) NOT NULL,
discount DECIMAL (10, 2) NOT NULL
DEFAULT 0,
CHECK (list_price >= discount AND
discount >= 0 AND
list_price >= 0)
);

在本例中,检查约束是在表级别定义的:

CHECK (list_price >= discount AND 
discount >= 0 AND
list_price >= 0)

检查约束确保标价始终大于或等于折扣,折扣和标价都大于或等于零。

以下语句违反了检查约束,因为折扣高于标价。

INSERT INTO products(product_name, list_price, discount)
VALUES('New Product',900,1000);

以下语句也违反了检查约束,因为折扣为负:

INSERT INTO products(product_name, list_price, discount)
VALUES('New XFactor',1000,-10);

向现有表添加检查约束

从3.25.2版开始,SQLite不支持向现有表添加检查约束。

但是,您可以按照以下步骤操作:

首先,创建一个新表,其结构与要添加检查约束的表相同。新表还应包括检查约束:

CREATE TABLE new_table (
[...],
CHECK ([...])
);

要获得旧表的结构,可以使用。模式命令。有关更多信息,请查看SQLite描述表教程。

其次,将数据从旧表复制到新表。

INSERT INTO new_table SELECT * FROM old_table;

第三,放下旧桌子:

DROP TABLE old_table;

第四,将新表重命名为旧表:

ALTER TABLE new_table RENAME TO old_table;

要使上述所有语句在事务中安全,您应该在如下事务中执行所有语句:

BEGIN;
-- create a new table
CREATE TABLE new_table (
[...],
CHECK ([...])
);
-- copy data from old table to the new one
INSERT INTO new_table SELECT * FROM old_table;

-- drop the old table
DROP TABLE old_table;

-- rename new table to the old one
ALTER TABLE new_table RENAME TO old_table;

-- commit changes
COMMIT;

在本教程中,您学习了如何使用SQLite检查约束来确保一列或一组列中的值满足表达式定义的条件。