Sql 简明教程

SQL - Check Constraint

The SQL CHECK Constraint

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

The SQL CHECK constraint is used to add conditions on a column of a table.

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

Once you add the check constraint on a column, it ensures that the data entered into the column meets the specified conditions. If a particular record does not meet the conditions, the database will prevent you from inserting or updating that record.

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

Suppose we have a table CUSTOMERS having a column AGE. We can add a CHECK constraint on this column to ensure that the age entered is always a positive number and not greater than 50 years. If someone tries to input a negative age or an age over 50, the database will reject it, ensuring that your data remains accurate and valid.

Check Constraint on Single Column

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

To add a check constraint on a column level, we have to specify the check constraint just after the column name during table creation.

Syntax

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

Following is the syntax to specify the check constraint on a single column −

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

Example

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

In the following query, we are creating a table named CUSTOMERS. Here, we are specifying a column-level check constraint on the AGE column, that allows only those records to be inserted where the age value of the customer is greater than "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 数据库中使用以下查询 −

To verify whether the check constraint is added to the AGE column, we can use the following query in the MySQL database −

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

Output

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

The above query will show all the details of the CUSTOMERS table, including how many columns have check constraints and what constraints we have specified in the table as shown below −

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 中插入一条记录 −

Now, to verify if the CHECK constraint is working properly, let us insert a record into CUSTOMERS where AGE contains a value less than 20 (does not satisfy the given condition) −

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

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

The output of the above query is as shown below −

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

Check Constraint on Multiple Columns

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

We can also add check constraint on multiple columns of a table by specifying the conditions that must be met for the combination of values in those columns.

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

Suppose we have a table containing the details of products, including their start and end dates. We can add a CHECK constraint that ensures the end date is always greater than or equal to the start date. In this case, the constraint is checking the values in two columns (start date and end date) within the same row to make sure they follow a specific relationship.

Example

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

In the following example, we are specifying a column-level check constraint on multiple columns (AGE and SALARY) of the CUSTOMERS table. Here, the AGE column will allow only those records where the AGE is greater than or equal to 20, and the SALARY column will allow only those records where the SALARY is greater than 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 数据库中使用以下查询 −

To verify whether the check constraint is applied on both the columns, we can use the following query in the MySQL database −

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

Output

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

It will show all the details of the created table, including how many columns have check constraints and what constraints we have specified in the table −

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 的值。

Now, we are inserting values into the CUSTOMERS table where the age is less than 20 and the salary is less than 20000.

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

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

The above query throws an error because the values passed in the AGE and the SALARY columns are not satisfying the CHECK constraints −

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

Check Constraint at the Table Level

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

We must use the check constraint before completing the table creation in order to ensure the check constraint at the table level.

Syntax

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

Following is the syntax to specify the check constraint on the table level −

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”的记录−

In the following SQL query, we are creating a table PRODUCTS. In here, we are specifying a table level check constraint on the DATE_OF_ORDER column, that allows only those records to be inserted where the DATE_OF_ORDER is less than (before) "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 约束−

We can verify the CHECK constraint on the created table using the following SQL query −

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

Output

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

It will show all the details of the created table, including how many columns have check constraints on the table level as shown below −

TABLE_NAME

CONSTRAINT_TYPE

CONSTRAINT_NAME

products

PRIMARY KEY

PRIMARY

products

CHECK

Constraint_DOO

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

In here, we are inserting values in the PRODUCTS which have the constraint less than "2023-02-09" on the column DATE_OF_ORDER −

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

以下是以上查询的输出：

Following is the output of the above query −

Query OK, 1 row affected (0.01 sec)

Check Constraint on an Existing Column

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

We can use the ALTER TABLE statement to add the check constraint on an existing column of the table.

Syntax

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

Following is the Syntax to add a check-constraint on an existing table −

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

Example

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

In the following query, we are creating a table named 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 约束，我们使用以下查询−

To add a check constraint on the AGE column, we are using the following query −

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

Verification

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

To verify whether the check constraint is applied after the table creation, use the following SQL query −

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

Output

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

It will display all of the table’s information, including the constraint we added to the age column −

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 语句。

If there is a way to add a constraint on a column, then you must also be able to remove the constraint from that column. To do that, you can use the ALTER DROP statement.

Syntax

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

Following is the syntax to remove a check constraint from the table −

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example

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

Following example shows how to drop the check constraint from the CUSTOMERS table created above −

ALTER TABLE CUSTOMERS
DROP CONSTRAINT Constraint_Age;

Verification

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

Using the following SQL query, we are verifying whether the constraint is removed −

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

Output

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

We can see that the check constraint added on the age column is removed −

TABLE_NAME

CONSTRAINT_TYPE

CONSTRAINT_NAME

customers

PRIMARY KEY

PRIMARY

customers

PRIMARY KEY

PRIMARY

customers

PRIMARY KEY

PRIMARY

customers

PRIMARY KEY

PRIMARY