How to check if a word is in a set of allowed words during INSERT INTO table VALUES (....)?

Question

Obviously that would happen during the creation of the table.

CREATE TABLE Employees(
    e_id INT NOT NULL AUTO_INCREMENT,
    e_name VARCHAR(20) NOT NULL,
    type CHAR(3) NOT NULL,
    CONSTRAINT type CHECK (type IN ('FOH, 'BOH', 'TCH', 'SHF', 'KSH', 'MOM', 'SIS')),
    PRIMARY KEY (e_id)
);

mysql doesn't let this work and I'm not sure how to do it. Basically, type is supposed to be a word only from one of the seven words ('FOH', 'BOH', ... etc).

Does anyone know how to solve this?

Is there any reason you're not using a separate lookup table for your allowed words with a foreign key in your Employees table? This would be more maintainable and allow for new allowed words to be added in future. — upsidedowncreature, Sep 18 '19 at 14:05
Possible duplicate of [How do I add a custom CHECK constraint on a MySQL table?](https://stackoverflow.com/questions/7522026/how-do-i-add-a-custom-check-constraint-on-a-mysql-table) — Ankit Bajpai, Sep 18 '19 at 14:11

score 0 · Answer 1 · answered Sep 18 '19 at 14:10

Try it without the CONSTRAINT:

type CHAR(3) NOT NULL,
CHECK (type IN ('FOH, 'BOH', 'TCH', 'SHF', 'KSH', 'MOM', 'SIS'))

or by changing the CONSTRAINT name

type CHAR(3) NOT NULL,
CONSTRAINT type2 CHECK (type IN ('FOH, 'BOH', 'TCH', 'SHF', 'KSH', 'MOM', 'SIS'))

Ankit Bajpai · Answer 2 · 2019-09-18T19:23:28.480

0

Your syntax seems absolutely correct except a minor correction for specifying the constraint name -

CREATE TABLE Employees(
    e_id INT NOT NULL AUTO_INCREMENT,
    e_name VARCHAR(20) NOT NULL,
    type CHAR(3) NOT NULL,
    CONSTRAINT type_values CHECK (type IN ('FOH', 'BOH', 'TCH', 'SHF', 'KSH', 'MOM', 'SIS')),
    PRIMARY KEY (e_id)
);

However MySQL doesn't support CHECK constraint yet. There is some workaround for it. Please check below link for details -

How do I add a custom CHECK constraint on a MySQL table?

edited Sep 18 '19 at 19:23

answered Sep 18 '19 at 14:10

Ankit Bajpai

13,128
4
25
40

MySQL has started supporting [CHECK constraint, since version 8.0.16](https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html) – Madhur Bhaiya Sep 18 '19 at 14:26

How to check if a word is in a set of allowed words during INSERT INTO table VALUES (....)?

2 Answers2