Does an enum in MySQL need to be NOT NULL?

user_105033 0
Viewed 25924

Lets say I have this:

ALTER TABLE asdf ADD field ENUM('Y', 'N') DEFAULT 'N';

Is putting a NOT NULL on the end necessary as it can only be Y and N?

EDT: based on comments, if I know the software always sets it to 'N' or 'Y' and is hardcoded in then is it OK to leave it off or could it still potentially become null some how.

mysql
5 Answers

MySQL will allow the value to be NULL if you do not specify NOT NULL in the column definition.

Here's a quick test:

mysql> create table test (id serial, field ENUM('Y','N') DEFAULT 'N');
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test (field) VALUES ('Y');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (field) VALUES ('N');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test () VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (field) VALUES (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (field) VALUES ('Invalid');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'field' at row 1 | 
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test;
+----+-------+
| id | field |
+----+-------+
|  1 | Y     | 
|  2 | N     | 
|  3 | N     | 
|  4 | NULL  | 
|  5 |       | 
+----+-------+
5 rows in set (0.00 sec)

So MySQL does respect the default value, but also allows NULLs. (Interestingly, it will truncate invalid values and allow blank strings as well, but that's a different issue)

user_706050

In ENUM data type, if you do not declare NOT NULL then it gives the default value NULL. However, if you declare NOT NULL then it gives the first value from the ENUM.

user_114792240

Nope, not necessary at all, it'll just default it to 'N' as you're probably expecting.

Edit: Commenter made me go off and test this, you should add not null unless you want null to be a valid value. Having default 'N' will default it to N if you leave the column out of your insert SQL, but if you set the value to null in the insert or update it will insert a row with null for the value, as you might not want.

Parrots_657630
Related