Mysql – Add default value to enum type field in schema builder

database-schemaenumslaravellaravel-4mysql

I'm using the following method to create a database column of type ENUM in schema builder:

$table->enum('status', array('new', 'active', 'disabled'));

I'd like to set it's default value to active.
I tried to do this:

$table->enum('status', array('new', 'active', 'disabled'))->default('active');

But as you can guess it doesn't set it's default value. I'm using a MySQL database if that's important.

Best Solution

From the MySQL manual:

If an ENUM column is declared to permit NULL, the NULL value is a legal value for the column, and the default value is NULL. If an ENUM column is declared NOT NULL, its default value is the first element of the list of permitted values.

I'm assuming this means you should set 'active' as the first value, remove the default() call, and possibly set NULL permittance manually.

Related Solutions

Mysql – How to set a default value for a MySQL Datetime column

IMPORTANT EDIT: It is now possible to achieve this with DATETIME fields since MySQL 5.6.5, take a look at the other post below...

Previous versions can't do that with DATETIME...

But you can do it with TIMESTAMP:

mysql> create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> desc test;
+-------+-------------+------+-----+-------------------+-------+
| Field | Type        | Null | Key | Default           | Extra |
+-------+-------------+------+-----+-------------------+-------+
| str   | varchar(32) | YES  |     | NULL              |       | 
| ts    | timestamp   | NO   |     | CURRENT_TIMESTAMP |       | 
+-------+-------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)

mysql> insert into test (str) values ("demo");
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| str  | ts                  |
+------+---------------------+
| demo | 2008-10-03 22:59:52 | 
+------+---------------------+
1 row in set (0.00 sec)

mysql>

CAVEAT: IF you define a column with CURRENT_TIMESTAMP ON as default, you will need to ALWAYS specify a value for this column or the value will automatically reset itself to "now()" on update. This means that if you do not want the value to change, your UPDATE statement must contain "[your column name] = [your column name]" (or some other value) or the value will become "now()". Weird, but true. I am using 5.5.56-MariaDB

MySQL ENUM type vs join tables

Changing the set of values in an ENUM requires an ALTER TABLE which might cause a table restructure -- an incredibly expensive operation (the table restructure doesn't happen if you simply add one new value to the end of the ENUM definition, but if you delete one, or change the order, it does a table restructure). Whereas Changing the set of values in a lookup table is as simple as INSERT or DELETE.
There's no way to associate other attributes with the values in an ENUM, like which ones are retired, and which ones are eligible to be put in a drop-down list in your user interface. However, a lookup table can include additional columns for such attributes.
It's very difficult to query an ENUM to get a list of distinct values, basically requiring you to query the data type definition from INFORMATION_SCHEMA, and parsing the list out of the BLOB returned. You could try SELECT DISTINCT status from your table, but that only gets status values currently in use, which might not be all values in the ENUM. However, if you keep values in a lookup table, it's easy to query, sort, etc.

I'm not a big fan of ENUM, as you can tell. :-)

The same applies to CHECK constraints that simply compare a column to a fixed set of values. Though MySQL doesn't support CHECK constraints anyway.

Update: MySQL 8.0.16 now implements CHECK constraints.

Best Solution

Related Solutions

Mysql – How to set a default value for a MySQL Datetime column

MySQL ENUM type vs join tables

Related Question