How to write mysql check constraints

MySQL check constraints (CHECK) can be implemented through the CREATE TABLE or ALTER TABLE statement and are defined according to the user's actual integrity requirements. It can enforce CHECK constraints on columns or tables individually. But the mysql manual writes very clearly: "All storage engines analyze the CHECK clause, but ignore the CHECK clause." So although it can be written like this, it will not have a restrictive effect.

Recommended tutorial: MySQL tutorial

Check constraints using the CHECK keyword. The specific syntax format is as follows:

CHECK <表达式>

Where: refers to the SQL expression, used to specify the qualifications that need to be checked.
If the CHECK constraint clause is placed after the definition of a column in the table, this constraint is also called a column-based CHECK constraint.
When updating table data, the system will check whether the updated data rows meet the constraints in the CHECK constraint. MySQL can use simple expressions to implement CHECK constraints, and also allows the use of complex expressions as qualifying conditions, such as adding subqueries to qualifying conditions.

Note: If the CHECK constraint clause is placed after the definition of all columns and the primary key constraint and foreign key definition, this constraint is also called a table-based CHECK constraint. This constraint can set qualifications on multiple columns in the table at the same time.

Set check constraints when creating a table

The syntax rules for setting check constraints when creating a table are as follows:

CHECK(<检查约束>)

Create tb_emp7 data in the test_db database table, the salary field value is required to be greater than 0 and less than 10000. The input SQL statement and running results are as follows.

mysql> CREATE TABLE tb_emp7
-> (
   -> id INT(11) PRIMARY KEY,
   -> name VARCHAR(25),
   -> deptId INT(11),
   -> salary FLOAT,
   -> CHECK(salary>0 AND salary<100),
   -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
   -> );
Query OK, 0 rows affected (0.37 sec)

Add check constraints when modifying the table

The syntax rules for setting check constraints when modifying the table are as follows:

ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)

Modify the tb_dept data table, requirements The value of the id field is greater than 0. The input SQL statement and running results are as follows.

mysql> ALTER TABLE tb_emp7
   -> ADD CONSTRAINT check_id
   -> CHECK(id>0);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

Solution: Use triggers to implement the CHECK check constraint function.

(1) Create tb_student (student information table).

-- 创建学生信息表
CREATE TABLE tb_student
(
   	id INT AUTO_INCREMENT PRIMARY KEY,
   	name VARCHAR(30),
   	age INT NOT NULL
);

(2) Create a trigger that checks whether the age (age) field is valid.

 -- 创建触发器 
CREATE TRIGGER trg_tb_student_insert_check BEFORE INSERT
ON tb_student FOR EACH ROW
BEGIN
   	DECLARE msg varchar(100);
 
   	IF NEW.age <= 0 OR NEW.age >= 100 
   	THEN
   	SET msg = CONCAT('您输入的年龄值：',NEW.age,' 为无效的年龄，请输入0到100以内的有效数字。');
   	SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg;
   	END IF;
END;

(3) Write test statements.

INSERT INTO tb_student(name,age) VALUES('Kevin',120);

The above is the detailed content of How to write mysql check constraints. For more information, please follow other related articles on the PHP Chinese website!