Search code examples
mysqlvalidation

Mysql restrict value of a field to be one of the defined ones

I am using mysql database.

I have a field user_type in USER table. I would like to restrict the values in this field to be one of ('ADMIN','AGENT','CUSTOMER').

The insert statements should fail if they tried to insert anything else than the above possible values. Also, I need defaulting to 'CUSTOMER' is none is specified in the insert statements.

The possible solution I could think of is use of triggers, but I would like to know How this could be handled more efficiently (possibly in the create table ddl itself?).

Any ideas, How to do this?

Solution

  • This is what the column type "enum" is for. You treat it like a string, and behind the scenes it is stored as an int, and must be one of the values defined in the DDL: 

    CREATE TABLE users (
  id int unsigned NOT NULL auto_increment primary key,
  user_type enum('ADMIN', 'AGENT', 'CUSTOMER') NOT NULL default 'CUSTOMER'
)

    Then insert like so: 

    INSERT INTO users (user_type) VALUES ('ADMIN'); // success
INSERT INTO users (user_type) VALUES ('ANONYMOUS'); // failure (or '' if not "strict" mode)
INSERT INTO users (user_type) VALUES (default(user_type)); // uses default
INSERT INTO users () VALUES (); // uses default
INSERT INTO users (user_type) VALUES (NULL); // failure

    note

    Note that for the query to actually fail, you must use "SQL strict mode". Otherwise, an "empty string" value (which is slightly special in that it has the numeric value of 0) is inserted.

    Quoting this docs page:

    When this manual refers to “strict mode,” it means a mode where at least one of STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled.