MySQL: Forbid empty string on insert when wrong enum value specified

user_1658532 0
Viewed 25

Is there a way to forbid empty string when wrong enum value is specified? whithout setting sql in "strict" or "traditional" mode

CREATE TABLE test (
 foo enum('aaa','bbb') NOT NULL
);

INSERT INTO test VALUES('asd');
mysql
2 Answers

You could write a CHECK constraint (requires MySQL 8.0):

mysql> set sql_mode='';

mysql> alter table test add check (foo != '');

mysql> INSERT INTO test VALUES('asd');
ERROR 3819 (HY000): Check constraint 'test_chk_1' is violated.

Or you could do something similar in a trigger.

But I recommend you just enable strict mode.

Bill_Karwin_208600

You can write your INSERT statement with a SELECT that will allow you to utilize a WHERE clause:

INSERT INTO test (foo) SELECT 'aaa' WHERE 'aaa' IN ('aaa', 'bbb');
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

INSERT INTO test (foo) SELECT 'ccc' WHERE 'ccc' IN ('aaa', 'bbb');
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
user_167155080
Related