Check constraints can be used to ensure the values to be inserted or update under a column are valid. For example, the values under the status column of the table s (supplier) must be between 1 and 100. To ensure that, a check constraint (highlighted) can be added to the table definition as below.
CREATE TABLE `s` (
`S_NUM` char(5) NOT NULL DEFAULT '',
`SNAME` char(20) DEFAULT NULL,
`STATUS` decimal(5,0) DEFAULT NULL,
`CITY` char(15) DEFAULT NULL,
PRIMARY KEY (`S_NUM`),
CONSTRAINT `CHK_STATUS` CHECK (((`STATUS` > 1) and (`STATUS` < 100)))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> select * from s;
+-------+-------+--------+--------+
| S_NUM | SNAME | STATUS | CITY |
+-------+-------+--------+--------+
| S1 | Smith | 20 | London |
| S2 | Jones | 10 | Paris |
| S3 | Blake | 30 | Paris |
| S4 | Clark | 20 | London |
| S5 | Adams | 30 | Athens |
+-------+-------+--------+--------+
5 rows in set (0.00 sec)
mysql> update s set STATUS=200 where s_num = 'S5';
ERROR 3819 (HY000): Check constraint 'CHK_STATUS' is violated.
mysql> show create table s;
mysql> SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'CHECK';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def | JDOE | CHK_STATUS | JDOE | s | CHECK | YES |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
1 row in set (0.01 sec)