Search code examples
mysqlcheck-constraints

How do I set Maximum Value of a Column in MYSQL?

I have Table called WORKERS, and the Table consists of totalNumberOfWorkers, i want to set the maximum value of the worker to be 30 and it shouldn't exceed over 30, it should be in the range of 0 to 30.

I have tried this, but it doesn't work and shows an error, my code isn't right.

ALTER TABLE WORKERS
CONSTRAINT WORKERS_CHECK CHECK (totalNumberOfWorkers => 0 AND totalNumberOfWorkers <31);

Here is the Table Called WORKERS

+------------------------+-------------+------+-----+---------+-------+
| Field                  | Type        | Null | Key | Default | Extra |
+------------------------+-------------+------+-----+---------+-------+
| WorkerID               | int(6)      | NO   | PRI | NULL    |       |
| dateOfWork             | date        | NO   |     | NULL    |       |
| timeOfWork             | time        | NO   |     | NULL    |       |
| descOfWorker           | varchar(50) | NO   |     | NULL    |       |
| totalNumberOfWorkers   | int(2)      | NO   |     | NULL    |       |
+------------------------+-------------+------+-----+---------+-------+
Solution

  • Found a solution 

    ALTER TABLE WORKERS
ADD CONSTRAINT TOTALNUMBER_CHECK1 CHECK(totalNumberOfWorkers BETWEEN 1 AND 30);