Sql 简明教程

SQL - Check Constraint

The SQL CHECK Constraint

SQL CHECK constraint 用于对表的列添加条件。

一旦在列上添加检查约束，它会确保输入该列的数据符合指定条件。如果某条具体记录不符合条件，数据库会阻止你插入或更新该记录。

假设我们有一个包含 AGE 列的 CUSTOMERS 表。我们可以在此列上添加 CHECK constraint 以确保输入的年龄始终为正数并且不超过 50 岁。如果有人尝试输入负年龄或 50 岁以上的年龄，数据库会拒绝，从而确保你的数据保持准确有效。

Check Constraint on Single Column

要在列级别添加检查约束，我们必须在表创建期间在列名之后指定检查约束。

Syntax

以下是指定单一列上 check constraint 的语法 −

CREATE TABLE table_name (
   column_name data_type CHECK (condition)
);

Example

在以下查询中，我们正在创建一个名为 CUSTOMERS 的表。此处，我们正在 AGE 列上指定列级检查约束，它只允许插入那些客户年龄大于“20”的记录 −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL CHECK(AGE>=20),
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Verification

要验证是否已将检查约束添加到 AGE 列，我们可以在 MySQL 数据库中使用以下查询 −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='CUSTOMERS';

Output

上面的查询将显示 CUSTOMERS 表的所有详细信息，包括有多少列具有检查约束，以及我们如何在表中指定约束，如下所示 −

TABLE_NAME

CONSTRAINT_TYPE

CONSTRAINT_NAME

customers

PRIMARY KEY

PRIMARY

customers

PRIMARY KEY

PRIMARY

customers

PRIMARY KEY

PRIMARY

customers

PRIMARY KEY

PRIMARY

customers

CHECK

employees_chk_1

现在，要验证 CHECK 约束是否正常工作，让我们在 AGE 包含小于 20 的值（不满足给定条件）的情况下向 CUSTOMERS 中插入一条记录 −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, 'Ramesh', 15, 'Ahmedabad', 2000.00 );

上面查询的输出如下所示 −

ERROR 3819 (HY000): Check constraint 'customers_chk_1' is violated.

Check Constraint on Multiple Columns

我们还可以通过指定多个列必须满足的条件，对表的多个列添加检查约束。

假设我们有一个包含产品详细信息的表，包括它们的开始和结束日期。我们可以添加一个 CHECK 约束，以确保结束日期始终大于或等于开始日期。在这种情况下，该约束正在检查同一行中的两列（开始日期和结束日期）的值，以确保它们遵循特定的关系。

Example

在以下示例中，我们正在对 CUSTOMERS 表的多个列（AGE 和 SALARY）指定列级检查约束。此处，AGE 列只允许 AGE 大于或等于 20 的记录，而 SALARY 列只允许 SALARY 大于 20000 的记录 −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL CHECK(AGE >= 20),
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2) CHECK(SALARY >= 20000),
   PRIMARY KEY (ID)
);

Verification

要验证是否已对这各个列应用检查约束，我们可以在 MySQL 数据库中使用以下查询 −

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='CUSTOMERS';

Output

它将显示已创建表的的所有详细信息，包括有多少列具有检查约束，以及我们在表中指定了哪些约束 −

TABLE_NAME

CONSTRAINT_TYPE

CONSTRAINT_NAME

customers

PRIMARY KEY

PRIMARY

customers

PRIMARY KEY

PRIMARY

customers

PRIMARY KEY

PRIMARY

customers

PRIMARY KEY

PRIMARY

customers

CHECK

customers_chk_1

customers

CHECK

customers_chk_2

现在，我们正在向 CUSTOMERS 表中插入年龄小于 20 岁且工资小于 20000 的值。

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 15, 'Ahmedabad', 2000.00 );

上述查询会发出错误，因为在 AGE 和 SALARY 列中传递的值不满足 CHECK 约束−

ERROR 3819 (HY000): Check constraint 'customers_chk_1' is violated.

Check Constraint at the Table Level

我们必须在完成表创建之前使用 check 约束，以确保在表级别实施 check 约束。

Syntax

以下是在表级别指定 check constraint 的语法−

CREATE TABLE table_name (
   column1 data_type,
   column2 data_type,...,
   CONSTRAINT constraint_name CHECK(column_name condition_value)
);

Example

在下面的 SQL 查询中，我们创建一个 PRODUCTS 表。在此，我们在 DATE_OF_ORDER 列上指定一个表级别 check 约束，它允许仅插入那些 DATE_OF_ORDER 小于（在之前）“2023-02-09”的记录−

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')
);

Verification

我们可以使用以下 SQL 查询验证已创建表的 CHECK 约束−

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='PRODUCTS';

Output

它将显示已创建表的全部详细信息，包括有多少列像下面显示的那样在表级别有 check 约束−

TABLE_NAME

CONSTRAINT_TYPE

CONSTRAINT_NAME

products

PRIMARY KEY

PRIMARY

products

CHECK

Constraint_DOO

在这里，我们在 PRODUCTS 中插入了在 DATE_OF_ORDER 列上有约束 less than "2023-02-09" 的值−

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

以下是以上查询的输出：

Query OK, 1 row affected (0.01 sec)

Check Constraint on an Existing Column

我们可以使用 ALTER TABLE 语句来向表的现有列添加 check 约束。

Syntax

以下是在现有表中添加 check 约束的语法−

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK(ColumnName condition_value);

Example

在下面的查询中，我们创建一个名为 CUSTOMERS 的表−

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

为了向 AGE 列中添加 check 约束，我们使用以下查询−

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

Verification

为了验证在表创建后是否应用了 check 约束，请使用以下 SQL 查询−

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='CUSTOMERS';

Output

它将显示表的全部信息，包括我们向年龄列中添加的约束−

TABLE_NAME

CONSTRAINT_TYPE

CONSTRAINT_NAME

customers

PRIMARY KEY

PRIMARY

customers

PRIMARY KEY

PRIMARY

customers

PRIMARY KEY

PRIMARY

customers

PRIMARY KEY

PRIMARY

customers

CHECK

Constraint_Age

Removing a Check Constraint

如果有一种方法可以向列中添加约束，那么你一定也能够从该列中删除该约束。为此，你可以使用 ALTER DROP 语句。

Syntax

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

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example

以下示例展示如何从上面创建的 CUSTOMERS 表中取消 check 约束−

ALTER TABLE CUSTOMERS
DROP CONSTRAINT Constraint_Age;

Verification

使用以下 SQL 查询，我们验证该约束是否已被删除−

SELECT table_name, constraint_type, constraint_name
FROM information_schema.table_constraints
WHERE table_name='CUSTOMERS';

Output

我们可以看到添加到年龄列中的 check 约束已删除−

TABLE_NAME

CONSTRAINT_TYPE

CONSTRAINT_NAME

customers

PRIMARY KEY

PRIMARY

customers

PRIMARY KEY

PRIMARY

customers

PRIMARY KEY

PRIMARY

customers

PRIMARY KEY

PRIMARY