CODEWITHSUNDEEP
mysqlconstraints
Shantanu Shinde
Shantanu Shinde

Reputation: 1012

MYSQL 8.0.17 CHECK not working even though it has been implemented

The CHECK constraints have been completely implemented since 8.0.16 according to the manual of MYSQL. But when I tried creating a table using that, it is not showing any error when the CHECK fails. What is going wrong? Here is my code to create table:

CREATE TABLE Employee (ENUM INT(4) AUTO_INCREMENT, 
FNAME varchar(10) NOT NULL, 
MNAME varchar(1) DEFAULT NULL, 
LNAME varchar(10) NOT NULL, 
BIRTHDATE date NOT NULL, 
GENDER char(1) CHECK(GENDER IN ('M', 'F')), 
SSN varchar(10) UNIQUE NOT NULL,
JOB_TITLE varchar(20) CHECK(JOB_TITLE IN ('Lecturer', 'Professor', 'Asst. Professor', 'Sr. Lecturer')),  
SALARY numeric(10, 2) NOT NULL, 
HIREDATE date NOT NULL, 
TAX numeric(8, 2) NOT NULL, 
DEPARTMENT varchar(20) CHECK(DEPARTMENT IN ('Biotechnology', 'Computer Science', 'Nano Technology', 'Information Technology')), 
PRIMARY KEY(ENUM));

Here, JOB_TITLE has the check to be in the list and same with DEPARTMENT. But this 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');

is being accepted without any error. why is this so?

Upvotes: 1

Views: 1684

Answers (1)

Dave Stokes
Dave Stokes

Reputation: 823

Try

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', 
                     'Professor',
                     1200900,
                     date '1990-12-16', 
                     120090, 
                     'Computer Science'
                     );

employee_chk_2 is violated in your version of the query as you are trying to use 'Prof.' instead of 'Professor'. And employee_chk_3 is violated because you are trying to insert 'Computer' instead of 'Computer Science'. The check constraints are working as you established them.

Upvotes: 1

Related Questions