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

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)

Answer 2

To set a default value 'N' for the column with a NOT NULL constraint, do this:

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

Explanation: According to the MySQL reference manual:

If an ENUM column is declared NOT NULL, its default value is the first element of the list of permitted