What is the use of check constraints in mysql

The role of check constraints in mysql: used to specify the range of possible values ​​for a column. Check constraints can be implemented through CREATE TABLE or ALTER TABLE statements, defined according to the user's actual integrity requirements.

In MySQL, CHECK check constraints are provided to specify the range of possible values ​​​​of a certain column. It enforces this by limiting the values ​​entered into the column. domain integrity. However, the current MySQL version only analyzes and processes the CHECK constraint, but it will be directly ignored and no error will be reported.

MySQL check constraints (CHECK) can be implemented through the CREATE TABLE or ALTER TABLE statement, defined according to the user's actual integrity requirements. It can enforce CHECK constraints on columns or tables individually.

Set check constraints when creating the table

Basic syntax:

CHECK(<检查约束>)

Example: 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

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

Basic syntax:

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

Example: Modify the tb_dept data table, requiring the id field value to be greater than 0

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

Delete the check constraint

Basic syntax:

ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;

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