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 ;