Shulou(Shulou.com)05/31 Report--

MySQL 8 how to use check constraints, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

What is "check constraint"?

This is a new feature that specifies the condition under which values are checked before being inserted or updated into a row. If the result of the search condition for any row of the table is FALSE, the constraint may return an error (but if the result is UNKNOWN or TRUE, the constraint does not return an error).

This feature started running on MySQL 8.0.16, and in previous versions, we could create it, but it didn't work, which meant syntax was supported, but it didn't work.

Rules of use to keep in mind:

AUTO_INCREMENT self-incrementing column is not allowed to use

Referencing another column in another table is not allowed

Stored and user-defined functions are not allowed to use

Stored procedures and function parameters are not allowed to use

Subqueries are not allowed to use

Columns used for subsequent actions (ON UPDATE,ON DELETE) in foreign keys are not allowed

Evaluate this monitor for the next statements INSERT,UPDATE,REPLACE,LOAD DATA and LOAD XML. In addition, it will also be for INSERT IGNORE,UPDATE IGNORE,LOAD DATA … IGNORE and LOAD XML... IGNORE evaluates this monitoring constraint. For these statements, a warning occurs if the constraint evaluates to FALSE. Inserts or updates are skipped.

Look at some examples.

I created the following table to test this feature. As the example shows, this is very simple:

CREATE TABLE users (id int not null auto_increment, firstname varchar (50) not null, lastname varchar (50) not null, age TINYINT unsigned not null CONSTRAINT `check_ 1` CHECK (age > 15), gender ENUM ('masked,' F') not null, primary key (id)) engine = innodb

In this simple test, we can write or update rows only if age > 15:00.

Let's look at an example and try to insert age

INSERT INTO users SET firstname = 'Name1', lastname =' LastName1', age = 10, gender = 'ERROR 3819 (HY000): Check constraint' check_1' is violated.

To delete, use the next example:

ALTER TABLE users DROP CHECK check_1

Let's look at another example and add more logic to it. I changed the table with the next checklist:

ALTER TABLE users ADD CONSTRAINT gender_male CHECK (CASE WHEN gender ='M' THEN CASE WHEN age > = 21 THEN 1 ELSE 0 END ELSE 1 END = 1) ALTER TABLE users ADD CONSTRAINT gender_female CHECK (CASE WHEN gender = 'F'THEN CASE WHEN age > = 18 THEN 1 ELSE 0 END ELSE 1 END = 1)

We added more logic, which now depends on the sex and age columns. The CHECK monitoring constraint is satisfied when and only if the specified condition of the table row is evaluated as TRUE or UNKNOWN (for NULL column values), otherwise the constraint is violated.

Let's look at an example from the previous logic.

Mysql > INSERT INTO users SET firstname = 'Name2', lastname =' LastName2', age = 10, gender = 'Fitch; ERROR 3819 (HY000): Check constraint' gender_female' is violated. Mysql > INSERT INTO users SET firstname = 'Name3', lastname =' LastName3', age = 10, gender = 'gender_male' is violated; ERROR 3819 (HY000): Check constraint' gender_male' is violated.

As you can see in the ERROR message, MySQL is displaying the name of the CHECK constraint. You can use it from the application source code to debug errors and know from which CHECK failed.

Finally, this is the table structure:

CREATE TABLE `users` (`id` int (11) NOT NULL AUTO_INCREMENT, `firstname` varchar (50) NOT NULL, `lastname` varchar (50) NOT NULL, `age`tinyint (3) unsigned NOT NULL, `gender` enum ('Maureen') NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `gender_ female` CHECK ((case when (`gender` ='F`) then (case when (`age` > 18) then 1 else 0 end) else 1 end) = 1)) CONSTRAINT `gender_ male` CHECK ((case when (`gender` ='M') then (case when (`age` > 21) then 1 else 0 end) else 1 end) = 1)) ENGINE=InnoDB AUTO_INCREMENT=4

We can use this feature to add more logic to the table, but based on my previous experience as a programmer, I do not recommend adding logic to the table, because it is difficult to find or debug errors unless you cannot access the application code.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.