CODEWITHSUNDEEP
mysql
Wergio
Wergio

Reputation: 45

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

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');

Upvotes: 1

Views: 31

Answers (2)

George Sun
George Sun

Reputation: 1080

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

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562368

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.

Upvotes: 2

Related Questions