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;