MySQL - is there a way to implement a CHECK constraint after creating the table? I created a table and after entering all the data, I found that I had missed a CHECK constraint in the CREATE TABLE statement.

create table jims_faculty
( fac_id int primary key,
fac_name varchar(15) not null,
fac_address varchar(40),
fac_contact_no int,
fac_qualification varchar(30) default 'post graduate',
fac_designation varchar(30) default 'assistant professor',
Fac_dept varchar(15)  check(fac_dept in ('it' , 'mgmt')) ,
fac_experience int ,
fac_date_of_joining date not null,
fac_aadhar int unique);
fac_aadhar int unique);
 

The fac_experience field should not be less than 2 years is the condition that should be in the check constraint.

Should I recreate the table?  

Or is there another possibility ?
Assuming you are using MySQL &gt;= 8.16 when MySQL finally implmemented CHECK constraints, you can do this following: 

ALTER TABLE &lt;table_name&gt;
ADD CHECK (check criteria)


so in your case, it would be 

ALTER TABLE jims_faculty
CHECK (fac_experience &gt;= 2)


If you want to name the constraint, you can do this:

ALTER TABLE jims_faculty
ADD CONSTRAINT chk_fac_experience_gt_2 CHECK (fac_experience &gt;= 2); 


From the manual (incredibly) Prior to MySQL 8.0.16, CREATE TABLE permits only the following limited version of table CHECK constraint syntax, which is parsed and ignored: 

The information in this post was largely gleaned from here.