MySQL's method of using check constraints
This article will explain in detail the methods of using check constraints in MySQL. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.
In a database, a CHECK constraint is a constraint that constrains acceptable data values or data formats in one or some columns of a table (used to limit the range of values in a column).
In some cases, we need the input of the field in the specified range
For example, gender can only enter 'male' or 'female', and the balance can only be greater than 0.
For example, you can require that the postcode column of the authors table only allow you to enter a six-digit zip code.
In addition to programmatic control, we can also use CHECK constraints to standardize data.
However:
All mysql storage engines do not support check constraints. MySQL parses the check clause, but ignores it when inserting data, so check does not work, so there are two ways to implement data constraints:
1. In mysql constraints, such as using enum types or triggers, etc.
two。 Check and insert the data in the application.
Use ENUM to limit inserted values, but this approach can only be used for discrete data, not for range data
-- create a test table that specifies that the sex field can only be 'male' or 'female' CREATE TABLE `user` (`id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR (18) COLLATE utf8_estonian_ci NOT NULL, `sex` ENUM ('male', 'female') COLLATE utf8_estonian_ci DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_estonian_ci test: INSERT INTO `user` (`name`, `sex`) VALUES ('Xiuji', 'Xiuji') Result: error code: 1265Data truncated for column 'sex' at row 1
If we need to limit the scope of data, for example, the balance can only be greater than 100, we can use triggers.
DELIMITER $$CREATE TRIGGER `test`.`remaining _ beforeInsert`BeforeInsert` BEFORE INSERT ON `test`.`user`FOR EACH ROW BEGIN IF `user`.`remaining` < 100 THEN SET `user`.`remaining` = 100; END IF; END$$DELIMITER. This is how MySQL uses check constraints. I hope the above can be helpful and learn more. If you think the article is good, you can share it for more people to see.
