5.3 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 ('Awesome Free Bike', 0);
SQL Server报错：
The INSERT statement conflicted with the CHECK constraint "positive_price". The conflict occurred in database "SampleDb", table "test.products", column 'unit_price'.
--INSERT语句与CHECK约束“positive_price”冲突。冲突发生在数据库“SampleDb”、表“test.products”、列“unit_price”中。
出现错误的原因是单价不大于检查约束中指定的零。
以下语句工作正常，因为插入的unit_price(599)满足CHECK约束positive_price：
INSERT INTO test.products(product_name, unit_price)
VALUES ('Awesome Bike', 599);
SQL Server CHECK约束和NULL
检查约束拒绝导致布尔表达式计算结果为FALSE的值。
由于NULL的计算结果为UNKNOWN，因此可以在表达式中使用它来绕过约束。
如下，你可以成功插入一个单元价格为NULL的行：
INSERT INTO test.products(product_name, unit_price)
VALUES ('Another Awesome Bike', NULL);
输出：
(1 row affected)
SQL Server在unit_price列中插入了NULL，并且没有报错。
为了解决这个问题，您需要对unit_price列使用非空约束。
引用多列的CHECK约束
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 约束，请使用 ALTERTABLE ADDCONSTRINT 语句。
假设有如下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
);
使用以下语句给test.products表添加CHECK约束：
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的新列：
ALTER TABLE test.products
ADD CONSTRAINT valid_price
CHECK(unit_price > discounted_price);
移除CHECK约束
要移除检查约束，使用ALTER TABLE DROP CONSTRAINT语句：
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
如果为检查约束指定了特定名称，则可以在语句中引用该名称。
但是，如果没有为检查约束指定特定名称，则需要使用以下语句查找它：
EXEC sp_help 'table_name';
比如：
EXEC sp_help 'test.products';
该语句发布了大量信息，包括约束名称：
然后再通过如下语句删除约束：
ALTER TABLE test.products
DROP CONSTRAINT positive_price;
禁用插入或更新的检查约束
要禁用插入或更新的检查约束，请使用以下语句：
ALTER TABLE table_name
NOCHECK CONSTRAINT constraint_name;
以下语句禁用valid_price约束：
ALTER TABLE test.products
NO CHECK CONSTRAINT valid_price;
