Check Constraints Issues

Earlier I wrote about check constraints when MySQL 8.0.16 was released. But this week I noticed two different folks having similar problems with them. And sadly it is 'pilot error'.

The first was labeled  MYSQL 8.0.17 CHECK not working even though it has been implemented and a cursory glance may make one wonder what is going on with the database.

The table is set up with two constraints. And old timers will probably mutter something under their breath about using ENUMs but here they are:

 JOB_TITLE varchar(20) CHECK(JOB_TITLE IN ('Lecturer', 'Professor', 'Asst. Professor', 'Sr. Lecturer')),  

DEPARTMENT varchar(20) CHECK(DEPARTMENT IN ('Biotechnology', 'Computer Science', 'Nano Technology', 'Information Technology')), 

And if you cut-n-paste the table definition into MySQL Workbench or MySQL Shell, it is perfectly valid DDL. 

So the table is good. 

What about the query?

INSERT INTO Employee (FNAME, MNAME, LNAME, BIRTHDATE, GENDER, SSN, JOB_TITLE,  SALARY, HIREDATE, TAX, DEPARTMENT ) VALUES 
 ('Sangeet', 'R', 'Sharma', date '1965-11-08', 'M', '11MH456633', 'Prof', 1200900, date '1990-12-16', 120090, 'Computer');

At first glance the query looks good.  But notice the use of 'Prof' instead of 'Professor' and 'Computer' instead of 'Computer Science'.  The two respective constraints are are working as they are supposed to. That is why you see the error message ERROR: 3819: Check constraint 'employee_chk_2' is violated.

So how to fix?  Well you can re-write the DDL so that 'Prof' and 'Computer'.  Or you can make the data match the specifications. If you are going to the trouble to add a constraint you are sabotaging your own work by doing things like this.

The Second Issue

In another Stackoverflow post someone with this table CREATE TABLE Test( ID CHAR(4),     CHECK (CHAR_LENGTH(ID) = 4) );  was wondering why constraint checks were be a problem with insert into Test(ID) VALUES ('12345');   

And the error you get if you try the above? ERROR: 1406: Data too long for column 'ID' at row 1!

Well, this is not a case where a constraint check is behaving badly.  Look at the table definition.  ID is a four (4) CHAR column.  And the length of '12345' is not four! 

Now in the past MySQL was lax and would truncate that extra character and provide a warning.  And those warnings were often ignored.  MySQL had a bad reputation for doing that truncation and the SQL mode of the server was changed to a default setting that does not allow that truncation. Now the server tells you the data is too long for that column.  The constraint checks have not come into play at that stage as the server sees you trying to shove five characters into a four character space. 

So how to fix? 1) make ID a CHAR(5) and rewrite the constraint, 2) change the SQL mode to allow the server to truncate data, or 3) do not try to put five characters into a space you designed for four.

My Gripe

It is frustrating to see something like constraint checks that are a really useful tool being abused.  And it is frustrating as so many people search the web for answers with keywords and will not look at the manual.  In both of the examples above five minutes with the manual pages would have save a lot of effort.

MySQL 8.0.16 Check Constraints

Before MySQL 8.0.16 you could put constraint checks into your Data Definition Language (DDL) when creating tables but the server ignored them.  There was much gnashing of teeth as taunts of "It is not a real database" from other databases taunted the MySQL Community. 
 
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)