Does an enum in MySQL need to be NOT NULL?

Question

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.

Answer 1

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.

Answer 2

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)