CHANGE Vs. MODIFY in ALTER TABLE Command

Whenever I have to change a column in MySQL (which isn’t that often), I always forget the difference between ALTER COLUMN, CHANGE COLUMN, and MODIFY COLUMN. Here’s a handy reference.

ALTER COLUMN

Used to set or remove the default value for a column. Example:

ALTER TABLE MyTable ALTER COLUMN foo SET DEFAULT 'bar';
ALTER TABLE MyTable ALTER COLUMN foo DROP DEFAULT;

CHANGE COLUMN

Used to rename a column, change its datatype, or move it within the schema. Example:

ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL FIRST;
ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL AFTER baz;

MODIFY COLUMN

Used to do everything CHANGE COLUMN can, but without renaming the column. Example:

ALTER TABLE MyTable MODIFY COLUMN foo VARCHAR(32) NOT NULL AFTER baz;

The official documentation for ALTER TABLE (for MySQL 5.1) is here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s