But with 8.0.16 this has all changed. You can now have your data constraints checked by the server. Below is an example table with two constraints.
mysql>CREATE TABLE parts
(id int, cost decimal(5,2) not null check (cost > 0),
price decimal(5,2) not null check (price > 1.0)
);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into parts (id,cost,price) values (1,1.10,2.25);
Query OK, 1 row affected (0.03 sec)
mysql> insert into parts (id,cost,price) values (1,1.10,0.25);
ERROR 3819 (HY000): Check constraint 'parts_chk_2' is violated.
mysql> insert into parts (id,cost,price) values (2,-1.1,4.25);
ERROR 3819 (HY000): Check constraint 'parts_chk_1' is violated.
The first data input above does passes the constraints since both the cost and price columns are greater than the minimum. But not so with the next inserts, So the constraints work!
Finding Out What The Error Message Means
Now seeing Check constraint 'parts_chk_1' is violated. tells me the data is out of value. But how do I find out what parts_chk_1 is?The fist thing to check is the table itself.
mysql> show create table parts;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| parts | CREATE TABLE `parts` (
`id` int(11) DEFAULT NULL,
`cost` decimal(5,2) NOT NULL,
`price` decimal(5,2) NOT NULL,
CONSTRAINT `parts_chk_1` CHECK ((`cost` > 0)),
CONSTRAINT `parts_chk_2` CHECK ((`price` > 1.0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
And you can also see the information in the IS.
mysql> select * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS;
+--------------------+-------------------+-----------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CHECK_CLAUSE |
+--------------------+-------------------+-----------------+-----------------+
| def | davetest | xxx_chk_1 | (`age` > 18) |
| def | davetest | parts_chk_1 | (`cost` > 0) |
| def | davetest | parts_chk_2 | (`price` > 1.0) |
| def | davetest | client_chk_1 | (`age` > 17) |
+--------------------+-------------------+-----------------+-----------------+
4 rows in set (0.00 sec)
