Mysql 简明教程

MySQL - Check Constraint

The MySQL Check Constraint

MySQL 检查约束是可以应用于列的条件，以确保该列中插入或更新的数据满足指定的条件。如果不满足条件，数据库会拒绝操作以维护数据完整性。

The MySQL Check Constraint is a condition that can be applied to a column to ensure that the inserted or updated data in that column meets the specified condition. The database rejects the operation if the condition is not met to maintain data integrity.

Check Constraint with a Trigger

MySQL 中的一个触发器用于在数据库中针对特定事件（例如 INSERT、UPDATE 或 DELETE 操作）自动执行一组 SQL 语句。

A trigger in MySQL is used to automatically execute a set of SQL statements in response to specific events in the database, such as an INSERT, UPDATE, or DELETE operation.

带有触发器的检查约束允许我们基于数据更改自动执行操作。

A check constraint with a trigger allows us to perform actions automatically based on data changes.

Example

假设使用 CREATE TABLE 语句在 MySQL 数据库中创建了一个名为 CUSTOMERS 的表，如下所示：

Assume we have created a table with name CUSTOMERS in the MySQL database using CREATE TABLE statement as shown below −

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

以下查询使用 INSERT 语句将值插入 CUSTOMERS 表中 −

Following query inserts values into CUSTOMERS table using the INSERT statement −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

获得的表如下所示：

The table obtained is as shown below −

现在，我们将添加一个检查约束，以确保 CUSTOMERS 表中客户的年龄应大于或等于 18 岁。此外，我们将创建一个触发器，当尝试插入一个年龄小于 18 岁的记录时，它会引发错误并阻止插入 −

Now, we will add a check constraint to ensure that the age of customers in the CUSTOMERS table should be greater than or equal to 18. Additionally, we will create a trigger that, when an attempt is made to insert a record with an age less than 18, it will raise an error and prevent the insertion −

-- Creating a Trigger
DELIMITER //
CREATE TRIGGER check_age_trigger
BEFORE INSERT ON CUSTOMERS
FOR EACH ROW
BEGIN
   IF NEW.AGE < 18 THEN
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Age must be 18 or older';
   END IF;
END;
//
DELIMITER ;

-- Adding a Check Constraint
ALTER TABLE CUSTOMERS
ADD CONSTRAINT check_age_constraint CHECK (AGE >= 18);

Output

我们得到了如下输出 −

We get the output as shown below −

Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

Adding Check Constraint on Single Column

我们可以通过在创建表时在列名后指定检查约束来对列应用检查约束。

We can apply a check constraint on a column by specifying the check constraint after the column name at the time of table creation.

Syntax

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

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

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

Example

在此示例中，我们创建一个名为 EMPLOYEES 的表，并在一个列上指定列级检查约束 −

In this example, we are creating a table named EMPLOYEES and specifying a column-level check constraint on one column −

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

我们可以通过向 EMPLOYEES 表中插入一个不满足条件的值来验证检查约束是否正常工作 −

We can verify if the check constraint is working correctly by inserting a value into the EMPLOYEES table which does not satisfy the condition −

INSERT INTO EMPLOYEES
VALUES (1, 'John', 19, 'New York', '09182829109');

Output

获得的输出如下 −

The output obtained is as follows −

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

Adding Check Constraint on Multiple Columns

我们可以通过为每个列在列名后指定约束来向表的多个列添加检查约束。

We can add check constraint on multiple columns of a table by specifying the constraints for each column after the column name.

Example

在以下示例中，我们创建一个名为 STUDENTS 的表，并在多个列（AGE 和 FEE）上指定列级检查约束 −

In the following example, we are creating a table named STUDENTS and specifying a column-level check constraint on multiple columns (AGE and 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)
);

现在，我们可以插入记录，但是如果我们尝试插入违反这些约束的记录，则数据库将拒绝它。

Now, we can insert records, but if we attempt to insert a record that violates these constraints, the database will reject it.

在这里，我们插入一条有效记录 −

Here, we are inserting a valid record −

INSERT INTO STUDENTS
VALUES (001, 'Robert', 21, 'LA', 17000);

我们可以在下面的输出中看到插入成功，因为年龄在允许的范围内，并且费用满足指定条件 −

We can see in the output below that the insertion is successful because the age is within the allowed range, and the fee meets the specified condition −

Query OK, 1 row affected (0.01 sec)

在这里，我们尝试插入一条违反约束的记录 −

In here, we are attempting to insert a record violating constraints −

INSERT INTO STUDENTS
VALUES (002, 'James', 25, 'Barcelona', 10000);

我们可以看到插入失败，因为年龄超过 24 岁，违反了约束。

We can see that the insertion fails since the age exceeds 24, violating the constraint.

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

Adding Check Constraint on an Existing Table

我们还可以使用 ALTER 语句向 MySQL 中的现有表添加检查约束。我们必须确保约束满足表中现有记录。

We can also add a check constraint on an existing table in MySQL by using the ALTER statement. We must ensure that the constraint satisfy for the existing records in the table.

Syntax

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

Example

在以下示例中，我们向上面创建的 CUSTOMERS 表的 AGE 列添加检查约束 −

In the following example, we are adding a check constraint to the AGE column of the CUSTOMERS table created above −

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

Output

以下是上面代码的输出： -

Following is the output of the above code −

Query OK, 7 rows affected (0.04 sec)
Records: 7  Duplicates: 0  Warnings: 0

Dropping Check Constraint

我们可以使用 ALTER 语句和 DROP 语句删除现有约束。

We can remove an existing constraint by using the ALTER statement with the DROP statement.

Syntax

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

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

ALTER TABLE table_name
DROP CONSTRAINT constraint_set;

Example

以下示例从上述 CUSTOMERS 表中 AGE 列删除现有约束 −

Following example removes an existing constraint from the AGE column in the CUSTOMERS table created above −

ALTER TABLE CUSTOMERS
DROP CONSTRAINT Constraint_Age;

Output

执行上面的代码后，我们得到以下输出： -

After executing the above code, we get the following output −

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Check-Constraints Using a Client Program

我们还可以使用客户端程序执行检查约束。

We can also perform check-constraints using the client program.

Syntax

Example

以下是这些程序 −

Following are the programs −