ALTER TABLE - MySQL

Overview

ALTER TABLE modifies the structure or settings of an existing MySQL table. It allows you to add, drop, or modify columns, change data types, and alter other table properties.

Syntax

ALTER TABLE table_name
[options]
alter_specification [, alter_specification ...]

Options:

  • ADD – Adds new columns
  • DROP – Removes columns
  • MODIFY – Modifies existing columns
  • RENAME – Renames the table

alter_specification:

  • ADD COLUMN column_name data_type [options]
  • DROP COLUMN column_name
  • MODIFY COLUMN column_name data_type [options]
  • CHANGE OLD_COLUMN_NAME NEW_COLUMN_NAME data_type [options]

Options/Flags

  • NULL – Specifies that the column can contain null values
  • NOT NULL – Specifies that the column cannot contain null values
  • DEFAULT default_value – Sets a default value for the column
  • PRIMARY KEY – Marks the column as a primary key
  • UNIQUE – Specifies that the column values must be unique
  • INDEX – Creates an index on the column

Examples

Adding a column:

ALTER TABLE users ADD COLUMN age INT NOT NULL;

Dropping a column:

ALTER TABLE users DROP COLUMN address;

Modifying a column:

ALTER TABLE users MODIFY COLUMN name VARCHAR(255) NOT NULL;

Renaming a table:

ALTER TABLE old_table_name RENAME TO new_table_name;

Common Issues

  • Trying to alter a reserved word or keyword as a column name. Use backticks to escape such names.
  • Attempting to modify a column that is part of a foreign key constraint without updating the constraint.
  • Forgetting to specify the NOT NULL constraint for columns that cannot accept null values.

Integration

ALTER TABLE can be combined with other commands, such as CREATE INDEX and PRIMARY KEY, to create and manage table constraints and indexes. It can also be used in conjunction with RENAME to rename tables.

  • CREATE TABLE – Creates a new table
  • DROP TABLE – Removes an existing table
  • CREATE INDEX – Creates an index on a table
  • PRIMARY KEY – Sets the primary key for a table