SQL Server检查约束

在本教程中，将学习如何使用SQL Server CHECK约束来强制域完整性。

SQL Server CHECK约束简介

CHECK约束用于指定必须满足布尔表达式的列中的值。

例如，要求价格必须为正数值，可以使用：

CREATE SCHEMA test;
GO

CREATE TABLE test.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2) CHECK(unit_price > 0)
);

如上所见，CHECK约束定义位于数据类型之后。 它由CHECK关键字和括号中的逻辑表达式组成：

CHECK(unit_price > 0)

还可以使用CONSTRAINT关键字为约束分配单独的名称，如下所示：

CREATE TABLE test.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2) CONSTRAINT positive_price CHECK(unit_price > 0)
);

显式名称有助于对错误消息进行分类，并允许在要修改它们时引用约束。

如果未以这种方式指定约束名称，SQL Server会自动生成名称(随机字符)。

请参阅以下插入语句：

INSERT INTO test.products(product_name, unit_price)
VALUES ('HuaWei Mate P20', 0);

SQL Server发出以下错误：

The INSERT statement conflicted with the CHECK constraint "positive_price". The conflict occurred in database "bbtest", table "test.products", column 'unit_price'.

发生错误的原因是单价不大于CHECK约束中指定的0值。

以下语句可以正常工作，因为CHECK约束中定义的逻辑表达式的计算结果为TRUE：

INSERT INTO test.products(product_name, unit_price)
VALUES ('HuaWei Mate P20', 5199);

SQL Server CHECK约束和NULL

CHECK约束拒绝导致布尔表达式求值为FALSE的值。

因为NULL计算为UNKNOWN，所以可以在表达式中使用它来绕过约束。

例如，可以插入单价为NULL的产品，如以下查询中所示：

INSERT INTO test.products(product_name, unit_price)
VALUES ('HuaWei Mate P20', NULL);

上面语句能够成功地执行。

SQL Server将NULL插入unit_price列，但未返回错误。

要解决此问题，需要对unit_price列使用NOT NULL约束。

检查引用多列的约束

CHECK约束可以引用多个列。 例如，在test.products表中存储常规和折扣价格，并且希望确保折扣价格始终低于常规价格：

CREATE TABLE test.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2) CHECK(unit_price > 0),
    discounted_price DEC(10,2) CHECK(discounted_price > 0),
    CHECK(discounted_price < unit_price)
);

unit_price和discounted_price的前两个约束看起来应该很熟悉。

第三个约束使用未附加到特定列的新语法。它在逗号分隔列列表中显示为单独的行项目。

前两列约束是列约束，而第三列约束是表约束。

注意，可以将列约束写为表约束。 但是，不能将表约束写为列约束。 例如，可以按如下方式重写上述语句：

CREATE TABLE test.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2),
    discounted_price DEC(10,2),
    CHECK(unit_price > 0),
    CHECK(discounted_price > 0),
    CHECK(discounted_price > unit_price)
);

又或者，

CREATE TABLE test.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2),
    discounted_price DEC(10,2),
    CHECK(unit_price > 0),
    CHECK(discounted_price > 0 AND discounted_price > unit_price)
);

还可以使用与列约束相同的方式为表约束指定名称：

CREATE TABLE test.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2),
    discounted_price DEC(10,2),
    CHECK(unit_price > 0),
    CHECK(discounted_price > 0),
    CONSTRAINT valid_prices CHECK(discounted_price > unit_price)
);

将CHECK约束添加到现有表

要将CHECK约束添加到现有表，可使用ALTER TABLE ADD CONSTRAINT语句。

假设有以下一个名称test.products表：

CREATE TABLE test.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2) NOT NULL
);

要将CHECK约束添加到test.products表，请使用以下语句：

ALTER TABLE test.products
ADD CONSTRAINT positive_price CHECK(unit_price > 0);

要添加带有CHECK约束的新列，请使用以下语句：

ALTER TABLE test.products
ADD discounted_price DEC(10,2)
CHECK(discounted_price > 0);

要添加名为valid_price的CHECK约束，请使用以下语句：

ALTER TABLE test.products
ADD CONSTRAINT valid_price 
CHECK(unit_price > discounted_price);

删除CHECK约束

要删除CHECK约束，请使用ALTER TABLE DROP CONSTRAINT语句：

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

如果为CHECK约束指定了名称，则可以在语句中引用该名称。

但是，如果不确定有没有为CHECK约束指定名称，可使用以下语句查看：

EXEC sp_help 'table_name';

例如：

EXEC sp_help 'test.products';

此语句发出许多信息，包括约束名称：

以下语句删除positive_price约束：

ALTER TABLE test.products
DROP CONSTRAINT positive_price;

禁用CHECK约束以进行插入或更新

要禁用插入或更新的CHECK约束，请使用以下语句：

ALTER TABLE table_name
NOCHECK CONSTRAINT constraint_name;

以下语句禁用valid_price约束：

ALTER TABLE test.products
NO CHECK CONSTRAINT valid_price;