Shulou(Shulou.com)06/01 Report--

This article mainly tells you how to write a good mysql check constraint. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope that how to write a good mysql check constraint article can bring you some practical help.

Check the constraint using the CHECK keyword, the specific syntax format is as follows:

CHECK

Where: refers to the SQL expression, which is used to specify the qualification to be checked.

If the CHECK constraint clause is placed after the definition of a column in the table, the constraint is also called a column-based CHECK constraint.

When the table data is updated, the system checks whether the updated data row meets the qualification in the CHECK constraint. MySQL can use simple expressions to implement CHECK constraints, or allow complex expressions as qualifiers, such as adding subqueries to qualifiers.

Note: if the CHECK constraint clause is placed after all column definitions as well as primary and foreign key definitions, 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 ()

To create an tb_emp7 data table in the test_db database, the salary field value is required to be greater than 0 and less than 10000. The SQL statement entered and the result of the run are shown below.

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

Add check constraints when you modify a 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 to require that the value of the id field be greater than 0. Enter the SQL statement and run the results as shown below.

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 function of CHECK checking constraints.

(1) create tb_student (student information table).

-- create student information table CREATE TABLE tb_student (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR (30), age INT NOT NULL)

(2) create a trigger to check whether the age (age) field is valid.

-- create trigger CREATE TRIGGER trg_tb_student_insert_check BEFORE INSERTON tb_student FOR EACH ROWBEGIN DECLARE msg varchar; IF NEW.age = 100 THEN SET msg = CONCAT ('the age value you entered:', NEW.age,' is an invalid age, please enter a valid number between 0 and 100.) ; SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg; END IF;END

(3) write test statements.

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

How to write a good mysql check constraints will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.