How to use check constraints in MySQL

Methods for using check constraints in MySQL: 1. If the field range to which CHECK constraints are to be set is small and it is easier to enumerate all values, the type of the field can be set to enum type or set type. 2. If the field range for which CHECK constraints need to be set is large, and it is difficult to enumerate all values, use triggers instead of constraints to achieve data validity.

In some cases, we need fields to be entered within a specified range,

For example: gender can only be entered as 'male' or 'female' ', the balance can only be greater than 0 and other conditions,

For example: you can require that the postcode column of the authors table only allows the entry of six-digit postal codes.

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 will analyze the check clause but ignore it when inserting data, so check does not work. effect.

Two ways to solve this problem:

1. If the field range to be set for CHECK constraints is small and it is easier to list all values, you can consider The type of this field is set to the enumeration type enum() or the collection type set().

For example, the gender field can be set like this. Inserting values ​​other than the enumeration value will not be allowed.

CREATE TABLE `Student` (
  `Sno` char(8) NOT NULL,
  `Sname` varchar(10) NOT NULL,
  `Sex` enum('男','女') NOT NULL DEFAULT '男',
  `Age` tinyint(4) NOT NULL DEFAULT '20',
  `Phonenumber` char(12) DEFAULT NULL,
  `Sdept` varchar(20) NOT NULL,
  PRIMARY KEY (`Sno`),
  UNIQUE KEY `Phonenumber` (`Phonenumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. If the field range for which CHECK constraints need to be set is large, and it is difficult to enumerate all values, such as integers or a certain range, then triggers can only be used instead of constraints to achieve data validity.

DELIMITER $
create trigger studentcheck before insert on Student for each row
begin
if new.Age<15 or new.Age>30 then set new.Age=20;end if;
end $
DELIMITER ;

Test:

mysql> insert into Student(Sno,Sname,Sex,Age,Phonenumber,Sdept) values('5','Joe','m',0,'12345243912','CS');
ERROR 1265 (01000): Data truncated for column 'Sex' at row 1
mysql> insert into Student(Sno,Sname,Sex,Age,Phonenumber,Sdept) values('5','Joe','男',0,'12345243912','CS');
Query OK, 1 row affected (0.00 sec)

mysql> select * from Student;
+-----+-------+-----+-----+-------------+-------+
| Sno | Sname | Sex | Age | Phonenumber | Sdept |
+-----+-------+-----+-----+-------------+-------+
| 4   | nancy | m   |   0 | 12345243965 | CS    |
| 5   | Joe   | 男  |  20 | 12345243912 | CS    |
+-----+-------+-----+-----+-------------+-------+
2 rows in set (0.00 sec)

It can be seen that now Sex must be selected between "male" and "female", otherwise the insertion fails; when the age does not meet the specification, due to the existence of the trigger, It will be automatically set to the default 20.

This can solve the problem of invalid check constraints.

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