×
☰ See All Chapters

MySQL CHECK Constraint

You can utilize check (CHK) constraints to check the validity of data entered into particular table columns. The check constraint is a way of providing another protective layer for the data.

MySQL CHECK Constraint Example 1

In the below example check constraint has been placed on the EMP_ZIP column of the table, ensuring that all employees entered into this table have a ZIP Code of ‘46234’.

CREATE TABLE EMPLOYEE_CHECK_TST

(EMP_ID CHAR(9) NOT NULL,

EMP_NAME VARCHAR(40) NOT NULL,

EMP_CITY VARCHAR(15) NOT NULL,

EMP_ST CHAR(2) NOT NULL,

EMP_ZIP NUMERIC(5) NOT NULL,

PRIMARY KEY (EMP_ID),

CONSTRAINT CHK_EMP_ZIP CHECK ( EMP_ZIP = 46234)

);

Above example can be implemented using ALTER statement as below:

CREATE TABLE EMPLOYEE_CHECK_TST

(EMP_ID CHAR(9) NOT NULL,

EMP_NAME VARCHAR(40) NOT NULL,

EMP_CITY VARCHAR(15) NOT NULL,

EMP_ST CHAR(2) NOT NULL,

EMP_ZIP NUMERIC(5) NOT NULL,

PRIMARY KEY (EMP_ID),

);

ALTER TABLE EMPLOYEE_CHECK_TST ADD

(CONSTRAINT CHK_ EMP_ZIP CHECK ( EMP_ZIP = 46234)

);

 

MySQL CHECK Constraint Example 2

In the below example check constraint has been placed on the EMP_ZIP column of the table, ensuring that all employees entered into this table have a ZIP Code from the list ('46234','46227','46745').

CREATE TABLE EMPLOYEE_CHECK_TST

(EMP_ID CHAR(9) NOT NULL,

EMP_NAME VARCHAR(40) NOT NULL,

EMP_CITY VARCHAR(15) NOT NULL,

EMP_ST CHAR(2) NOT NULL,

EMP_ZIP NUMERIC(5) NOT NULL,

PRIMARY KEY (EMP_ID),

CONSTRAINT CHK_EMP_ZIP CHECK ( EMP_ZIP in ('46234','46227','46745') );

);

 

MySQL CHECK Constraint Example 3

In the below example check constraint has been placed on the EMP_ZIP column of the table, ensuring that all employees entered into this table have a ZIP Code  greater than 4622 and lesser than 46800

CREATE TABLE EMPLOYEE_CHECK_TST

(EMP_ID CHAR(9) NOT NULL,

EMP_NAME VARCHAR(40) NOT NULL,

EMP_CITY VARCHAR(15) NOT NULL,

EMP_ST CHAR(2) NOT NULL,

EMP_ZIP NUMERIC(5) NOT NULL,

PRIMARY KEY (EMP_ID),

CONSTRAINT CHK_EMP_ZIP CHECK (EMP_ZIP > 4622  AND EMP_ZIP <= 46800 )) );

);

 

All Chapters
Author