CODEWITHSUNDEEP
mysql
useR
useR

Reputation: 3082

MySQL specify numeric input range when create table

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

Upvotes: 0

Views: 190

Answers (1)

Jim Grady
Jim Grady

Reputation: 210

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.

Upvotes: 1

Related Questions