Mysql – Remove default value from NOT NULL column

MySQL

I have an existing NOT NULL column which has a default value. I want to remove the default value so that the user is forced to enter a value.

mysql> SHOW CREATE TABLE items;
CREATE TABLE `items` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `ordering` int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`)
)

However, when I try to modify the column and set the default to null, mysql throws an error:

mysql> ALTER TABLE items MODIFY ordering INT(11) NOT NULL DEFAULT NULL;
ERROR 1067 (42000): Invalid default value for 'ordering'

How can I remove the default from this column without having to do something crazy like change it to nullable, then remove the default, then change it back to not null?

Best Answer

The answer is to use the following (slightly odd) syntax:

ALTER TABLE items ALTER ordering DROP DEFAULT;

Related Solutions

MySQL – Fixing current_timestamp Default Column Value Issue on Windows and Linux

The reason is the different versions installed in the two environments.

The option for DEFAULT CURRENT_TIMESTAMP for datetime columns was added in 5.6 version (specifically in 5.6.5). See the doumentation about the DATE, DATETIME, and TIMESTAMP Types:

The TIMESTAMP and (as of MySQL 5.6.5) DATETIME data types offer automatic initialization and updating to the current date and time. For more information, see Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

Most probably your Linux installation has an older version while the Windows has a newer. The solution would be to upgrade to the newer version (or have it installed besides the old).

General info about upgrading from 5.5 to 5.6 here: Upgrading from MySQL 5.5 to 5.6 and specific about upgrading in Ubuntu 14.04 LTS.

Don't forget to backup your data before you upgrade and run mysql_upgrade immediately after.

MySQL – Not Null Columns Accepting Null Values Issue

It actually doesn't accept NULL values it considers it as empty string. That's because you have your server in non-strict mode. That controls how MySQL handles invalid or missing values in inserts and updates. You can read more about modes here: http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict

mysql> insert into cities(state_id) values (20);
Query OK, 1 row affected, 1 warning (0.07 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1364 | Field 'name' doesn't have a default value |
+---------+------+-------------------------------------------+

mysql> select name is null from cities where id = LAST_INSERT_ID();
+--------------+
| name is null |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> SET sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into cities(state_id) values (20);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

Best Answer

Related Solutions

MySQL – Fixing current_timestamp Default Column Value Issue on Windows and Linux

MySQL – Not Null Columns Accepting Null Values Issue

Related Question