How to prevent empty values of enum column in MySQL

Question

mysql> CREATE TABLE foo ( f ENUM('a', '123') );

mysql> insert into foo(f) value(3);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from foo;
+------+
| f    |
+------+
|      |
+------+

How to make it produce a failure when inserting a value out of range?

Adriaan Stander · Accepted Answer · 2010-02-21 10:50:06Z

5

From 10.4.4. The ENUM Type

If strict SQL mode is enabled, attempts to insert invalid ENUM values result in an error.

answered Feb 21, 2010 at 10:50

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Daniel Vassallo Over a year ago

To check our current SQL mode at the mysql prompt type SELECT @@global.sql_mode;. To change the current SQL mode to strict type type SET @@global.sql_mode = “STRICT_ALL_TABLES”;

Whakkee Over a year ago

what is your warning? i copied your code, and I get an error message: "#1265 - Data truncated for column 'f' at row 1". The record is NOT inserted! My global sql mode is STRICT_TRANS_TABLES.

Nathan Villaescusa · Accepted Answer · 2011-01-19 19:49:15Z

1

This appears to be bug with mysql: http://bugs.mysql.com/bug.php?id=43880

answered Jan 19, 2011 at 19:49

Nathan Villaescusa

17.7k4 gold badges55 silver badges58 bronze badges

Collectives™ on Stack Overflow

How to prevent empty values of enum column in MySQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related