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.
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.
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.
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.
