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 COLUMNALTER [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 ;
