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

输出

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

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

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

输出

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

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

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

输出

我们可以看到年龄列上添加的检查约束被删除 -