SQL CHECK约束

CHECK约束 对于输入到特定表列的数据的有效性非常有用。检查约束是提供数据的另一层保护的方式。

如果我们在列上指定了检查约束，则只允许在该列中插入特定的值。

检查约束仅应用于单个列，但在单个表中可能存在多个检查约束的可能性。

在列上添加检查约束

要在列级别应用检查约束，我们必须在表创建过程中在列名后面指定检查约束。

语法

以下是在列上指定检查约束的语法。

CREATE TABLE table_name (
   column1 datatype(size),
   column datatype(size) constraint constraintName CHECK Check(columnName    condition value),
   …, column datatype (size));

示例

在以下的SQL查询中，我们正在创建一个名为employees的表，并在一个列上指定列级别的检查约束。

CREATE TABLE employees(
   EID INT NOT NULL,
   NAME VARCHAR(40),
   AGE INT NOT NULL CHECK(AGE>=20),
   CITY VARCHAR(30),
   C_Phone VARCHAR(12) NOT NULL UNIQUE);

在上面的查询中，我们在age列上指定了检查约束。根据此约束，只有age大于20的记录才能被插入到age列中。

验证

要验证创建的表，我们可以使用以下SQL查询：

EXEC sp_help "dbo.employees";

这将显示创建的表的所有细节，包括有多少列具有检查约束以及我们在表中指定了什么约束。

+------------------------+--------------------------------+---------------+---------------+----------------+------------------------+----------------+
| constraint_type        | constraint_name                | delete_action | update_action | status_enabled | status_for_replication |constraint_keys |
+------------------------+--------------------------------+---------------+---------------+----------------+------------------------+----------------+
| CHECK on column AGE    | CK__employees__AGE__4CC05EF3   | (n/a)         | (n/a)         | Enabled        | Is_For_Replication     |([AGE]>=(20))   |
| UNIQUE (non-clustered) | UQ__employee__57B58178E24D3C4F | (n/a)         | (n/a)         | (n/a)          | (n/a)                  |   C_Phone      |
+------------------------+--------------------------------+---------------+---------------+----------------+------------------------+----------------+

检查多列约束

我们还可以在表的多列上添加检查约束。在以下示例中，我们正在创建一个名为students的表，并在多个列（AGE和FEE）上指定列级检查约束。

CREATE TABLE students(
   SID INT NOT NULL,
   NAME VARCHAR(20),
   AGE INT NOT NULL CHECK(AGE<=24),
   CITY VARCHAR(30),
   FEE NUMERIC NOT NULL CHECK(FEE>=15000));

根据上述创建的两个约束条件，年龄列只允许年龄小于24的记录，费用列只允许费用大于15,000的记录。

验证

为了验证创建的表，我们可以使用以下SQL查询：

EXEC sp_help "dbo.employees";

将展示创建表的所有细节，包括有多少列具有检查约束以及我们在表中指定的约束。

+------------------------+--------------------------------+---------------+---------------+----------------+------------------------+----------------+
| constraint_type        | constraint_name                | delete_action | update_action | status_enabled | status_for_replication |constraint_keys |
+------------------------+--------------------------------+---------------+---------------+----------------+------------------------+----------------+
| CHECK on column AGE    | CK__employees__AGE__4CC05EF3   | (n/a)         | (n/a)         | Enabled        | Is_For_Replication     |([AGE]<=(24))   |
| CHECK on column FEE    | CK__students__FEE__5090EFD7    | (n/a)         | (n/a)         | Enabled    | Is_For_Replication     |([FEE]>=(15000))|
+------------------------+--------------------------------+---------------+---------------+----------------+------------------------+----------------+

现在，我们正在尝试向年龄大于24岁且费用低于15,000的学生表中插入值。

INSERT INTO students VALUES(001, 'Aman Kumar', 25, 'Ranchi', 14000);

当我们尝试插入数据时，查询会报错，因为我们正在使用年龄age，其值大于24。

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK__students__AGE__4F9CCB9E". The conflict occurred in database "master", table "dbo.students", column 'AGE'.

在表级别上添加检查约束

在完成表的创建之前，我们必须使用检查约束以确保表级别上的检查约束。

语法

以下是指定表级别上的检查约束的语法

CREATE TABLE table_name(
   column1 datatype(size),
   column2 datatype(size),
   … columnN datatype, CONSTRAINT constraint_name CHECK(column_name condition value));

示例

我们正在创建一个表，并在以下SQL查询中对其中一个列指定表级检查约束。

CREATE TABLE products(
   PID INT NOT NULL,
   PNAME VARCHAR(30),
   DELIVERY_CITY VARCHAR(20),
   DATE_OF_ORDER Date NOT NULL,
   PRICE INT,
   PRIMARY KEY(PID),
   CONSTRAINT Constraint_DOO CHECK(DATE_OF_ORDER <= '2023-02-09'));

在上述SQL查询中，我们根据检查约束在DATE_OF_ORDER列上指定了一个检查约束，根据检查约束，只有DATE_OF_ORDER小于“2023-02-09”的记录才能被允许在此列中。

验证

为了验证创建的表，我们可以在SQL中使用以下查询 –

EXEC sp_help "dbo.products";

它将显示创建的表的所有细节，包括表级别上有多少列具有检查约束。

+-------------------------------+-----------------+---------------+---------------+----------------+------------------------+-------------------------------+
| constraint_type               | constraint_name | delete_action | update_action | status_enabled | status_for_replication |constraint_keys                |
+-------------------------------+-----------------+---------------+---------------+----------------+------------------------+-------------------------------+
| CHECK on column DATE_OF_ORDER | Constraint_DOO  | (n/a)         | (n/a)         | Enabled        | Is_For_Replication     |([DATE_OF_ORDER]<='2023-02-09')|
| PRIMARY KEY (clustered)       | PK__products    | (n/a)         | (n/a)         | (n/a)      | (n/a)                  |                           PID |
+-------------------------------+-----------------+---------------+---------------+----------------+------------------------+-------------------------------+

现在，我们正在编写一个查询，在学生表中插入值，其中Date_of_order大于（’2023-02-09’）。

INSERT INTO products VALUES(001, 'Nike Shoe', 'Ranchi', '2023-02-11', 2000);

查询在尝试插入数据时抛出错误，因为我们正在使用日期，其值大于’2023-02-09’。

在表创建后检查约束条件

假设出现了这样的场景，我们需要在创建表后或者数据库中已经存在的表上应用检查约束条件，SQL提供了一种方法来实现这一点。在这种情况下，我们可以使用ALTER语句来将检查约束条件应用到已经创建的表的列上。

语法

ALTER TABLE table_name ADD CONSTRAINT ConstraintName CHECK(ColumnName condition Value);

示例

考虑我们有一个带有年龄列的客户表。为了向年龄列添加一个检查约束，我们使用以下查询。

ALTER TABLE customers ADD CONSTRAINT Constraint_Age CHECK (AGE >= 21);

验证

使用以下SQL查询来检查新创建或更改的表：

EXEC sp_help 'dbo.customers';

它将显示表的所有信息，包括我们添加到年龄列的约束。

+---------------------+-----------------+---------------+---------------+----------------+------------------------+----------------+
| constraint_type     | constraint_name | delete_action | update_action | status_enabled | status_for_replication |constraint_keys |
+---------------------+-----------------+---------------+---------------+----------------+------------------------+----------------+
| CHECK on column AGE | Constraint_Age  | (n/a)         | (n/a)         | Enabled        | Is_For_Replication     |([AGE]>=(21)))  |
+---------------------+-----------------+---------------+---------------+----------------+------------------------+----------------+

删除检查约束

如果有一种方法可以在列上应用约束，那么您也必须能够从该列中删除约束。要实现这一点，可以使用ALTER… DROP语句。

语法

以下是从表中删除约束的语法：

ALTER TABLE table_name DROP CONSTRAINT constraint_set;

示例

以下示例演示了如何从上面创建的”Customers”表中删除检查约束。我们首先检查”Customers”表中是否有任何列具有检查约束，如果有，则可以使用ALTER命令将其删除。

ALTER TABLE customers drop CONSTRAINT Constraint_Age;

验证

在这里我们可以看到，在之前我们对年龄列添加了一个约束检查。然而，在使用上述的SQL查询之后，表中没有约束，如下表所示。

+-------------------------+---------------------------------+---------------+----------------+-----------------+
| constraint_type         | constraint_name                 | update_action | status_enabled | constraint_keys |
+-------------------------+---------------------------------+---------------+----------------+-----------------+
| PRIMARY KEY (clustered) | PK__CUSTOMER__3214EC270E48CD10  | (n/a)         | (n/a)          |    ID           |
+-------------------------+---------------------------------+---------------+----------------+-----------------+