SQL - 检查约束

---

---

SQL CHECK 约束

SQL 的CHECK 约束用于向表的列添加条件。

在列上添加检查约束后，它可确保输入到该列的数据满足指定的条件。如果特定记录不满足这些条件，则数据库将阻止您插入或更新该记录。

假设我们有一个名为 CUSTOMERS 的表，其中包含一个 AGE 列。我们可以在此列上添加CHECK 约束，以确保输入的年龄始终为正数且不超过 50 岁。如果有人试图输入负年龄或超过 50 岁的年龄，数据库将拒绝它，确保您的数据保持准确和有效。

单列检查约束

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

语法

以下是指定单列检查约束的语法：

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

示例

在下面的查询中，我们正在创建一个名为 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)
);

验证

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

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

输出

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

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

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 约束，以确保结束日期始终大于或等于开始日期。在这种情况下，约束正在检查同一行中两列（开始日期和结束日期）的值，以确保它们遵循特定的关系。

示例

在下面的示例中，我们正在 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)
);

验证

要验证检查约束是否应用于这两列，我们可以在 MySQL 数据库中使用以下查询：

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

输出

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

现在，我们正在向 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.

表级检查约束

为了确保表级检查约束，我们必须在完成表创建之前使用检查约束。

语法

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

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

示例

在以下 SQL 查询中，我们正在创建一个名为 PRODUCTS 的表。在这里，我们正在 DATE_OF_ORDER 列上指定表级检查约束，该约束仅允许插入 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')
);

验证

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

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

输出

它将显示已创建表的全部详细信息，包括表级别有多少列具有检查约束，如下所示：

在这里，我们正在向 PRODUCTS 中插入值，该值在 DATE_OF_ORDER 列上具有小于“2023-02-09”的约束：

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

以下是上述查询的输出：

Query OK, 1 row affected (0.01 sec)

现有列的检查约束

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

语法

以下是向现有表添加检查约束的语法：

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

示例

在以下查询中，我们正在创建一个名为 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 列添加检查约束，我们使用以下查询：

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

验证

要验证表创建后是否应用了检查约束，请使用以下 SQL 查询：

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

输出

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

删除检查约束

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

语法

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

ALTER TABLE table_name 
DROP CONSTRAINT constraint_name;

示例

下面的示例显示如何从上面创建的 CUSTOMERS 表中删除检查约束：

ALTER TABLE CUSTOMERS 
DROP CONSTRAINT Constraint_Age;

验证

使用以下 SQL 查询，我们正在验证约束是否已删除：

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

输出

我们可以看到，添加到年龄列的检查约束已删除：