2

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    |       |
+------------------------+-------------+------+-----+---------+-------+
Aleks G
  • 56,435
  • 29
  • 168
  • 265
Tormare Bap
  • 97
  • 1
  • 11
  • 1
    _but it doesn't work and shows an error_ -> Please! [Edit] your question and add the error message. Always add the error message! – Ocaso Protal May 10 '19 at 13:09
  • What version of MySQL? Your constraint looks OK but check constraints don't work in MySQL [prior to version 8.0.16](https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html). – sticky bit May 10 '19 at 13:10
  • 1
    Possible duplicate of [Range constraint numeric MySQL column: how?](https://stackoverflow.com/questions/15758076/range-constraint-numeric-mysql-column-how) – Ocaso Protal May 10 '19 at 13:10
  • Another duplicate: https://stackoverflow.com/questions/9287784/give-an-int-table-column-in-mysql-a-range-of-allowed-values-from-1-to-9 – Ocaso Protal May 10 '19 at 13:12
  • You can do it with a `before insert` trigger. Also, specifying column as `int(2)` doesn't mean integer of 2 digits, it means `integer that's 4 bytes long, which is 2^32, but only 2 digits will be displayed, however you can store 4 billion values inside it`. You want to use `tinyint` and you should check the range of values here: https://dev.mysql.com/doc/refman/8.0/en/integer-types.html – Mjh May 10 '19 at 13:14

1 Answers1

3

Found a solution 

ALTER TABLE WORKERS
ADD CONSTRAINT TOTALNUMBER_CHECK1 CHECK(totalNumberOfWorkers BETWEEN 1 AND 30);
Ocaso Protal
  • 19,362
  • 8
  • 76
  • 83
Tormare Bap
  • 97
  • 1
  • 11
  • 1
    The problem with your original statement was that you had `=>` instead of `>=`. Note that although this statement doesn't cause an error, unless you are using MySQL 8.0.16+ MySQL will ignore it. See https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html – Nick May 11 '19 at 05:39