Remove default value from NOT NULL column

Question

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?

Answer 1

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

ALTER TABLE items ALTER ordering DROP DEFAULT;

Answer 2

When trying to modify a column with ALTER TABLE, there are 4 keywords that can be used, each with different capabilities:

• CHANGE [COLUMN]
• MODIFY [COLUMN]
• RENAME COLUMN
• ALTER [COLUMN]

CHANGE is a MySQL extension to standard SQL. MODIFY and RENAME COLUMN are MySQL extensions for Oracle compatibility.

ALTER [COLUMN] is standard SQL (I think).

The docs about ALTER TABLE have more details:

Renaming, Redefining, and Reordering Columns

The CHANGE, MODIFY, RENAME COLUMN, and ALTER clauses enable the names and definitions of existing columns to be altered. They have these comparative characteristics:

• CHANGE:

  • Can rename a column and change its definition, or both.
  • Has more capability than MODIFY or RENAME COLUMN, but at the expense of convenience for some operations. CHANGE requires naming the column twice if not renaming it, and requires respecifying the column definition if only renaming it.
  • With FIRST or AFTER, can reorder columns.

• MODIFY:

  • Can change a column definition but not its name.
  • More convenient than CHANGE to change a column definition without renaming it.
  • With FIRST or AFTER, can reorder columns.

• RENAME COLUMN:

  • Can change a column name but not its definition.
  • More convenient than CHANGE to rename a column without changing its definition.

• ALTER:

  • Used only to change a column default value.

---

In this case, you have 3 options:

ALTER TABLE items
 CHANGE ordering ordering int NOT NULL;
ALTER TABLE items 
 MODIFY ordering int NOT NULL;
ALTER TABLE items
 ALTER ordering DROP DEFAULT ;

• SystemParadox: I never noticed that CHANGE/MODIFY remove the default if it is not specified! I'm amazed that hasn't bitten me yet.