MySQL specify numeric input range when create table

Question

For restrict the categorical input i employed ENUM while how do i deal with numeric range (integer or real number) e.g. 356-956 for Imp.

/**********************************************************************/
/*Create database and table with constraint*/

CREATE DATABASE Hactl;
CREATE TABLE  Hactl.table1(
    Type CHAR(1) ENUM('M','C') NOT NULL,
    Region VARCHAR(50) NOT NULL,
    Country VARCHAR(50) NOT NULL,
    City VARCHAR(3) NOT NULL,
    Imp DECIMAL(10,2) NOT NULL,
    Exp DECIMAL(10,2) NOT NULL,
    TS DECIMAL(10,2) NOT NULL,
    Year INT(4) NOT NULL,
    Month CHAR(3) ENUM('Jan', 'Feb', 'Mar',
               'Apr', 'May', 'Jun',
               'Jul', 'Aug', 'Sep',
               'Oct', 'Nov', 'Dec') NOT NULL,
);

Thanks

score 1 · Answer 1 · edited May 23 '17 at 10:26

1

What you're looking for is a CHECK constraint but unfortunately mysql doesn't support it.

So you don't have any particularly elegant option but you can use before insert/update triggers, or you can make a table containing allowed values and then use a foreign key constraint.

edited May 23 '17 at 10:26

Community

1
1

answered Sep 11 '14 at 03:30

Jim Grady

210
1
3

Oh and add your vote to this 10 year old (!) feature request for mysql to add check constraints :) http://bugs.mysql.com/bug.php?id=3464 – Jim Grady Sep 11 '14 at 03:32

MySQL specify numeric input range when create table

1 Answers1