MySQL ALTER TABLE Statement

The ALTER TABLE statement is used to modify an existing table's structure, such as adding, deleting, or modifying columns, as well as adding or removing constraints.

ALTER TABLE - ADD Column

To add a new column to an existing table, use the ADD clause. This is useful for expanding a table's schema by adding new attributes.

Syntax

ALTER TABLE table_name
ADD column_name datatype;

• table_name: The name of the table to modify.
• column_name: The name of the new column to add.
• datatype: The datatype of the new column (e.g., VARCHAR, INT, DATE).

Example

To add a new column email to the Customers table:

ALTER TABLE Customers
ADD email VARCHAR(255);

ALTER TABLE - DROP COLUMN

To delete an existing column from a table, use the DROP COLUMN clause. Note that some database systems may have limitations on deleting columns, especially if they are part of constraints or indexes.

Syntax

ALTER TABLE table_name
DROP COLUMN column_name;

• table_name: The name of the table from which to drop the column.
• column_name: The name of the column to drop.

Example

To remove the email column from the Customers table:

ALTER TABLE Customers
DROP COLUMN email;

ALTER TABLE - MODIFY COLUMN

To modify the definition of an existing column, such as changing its datatype, use the MODIFY COLUMN clause. This can be used to adjust the column's datatype, size, or other attributes.

Syntax

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

• table_name: The name of the table that contains the column to modify.
• column_name: The name of the column to modify.
• datatype: The new datatype for the column.

Example

To change the datatype of the email column in the Customers table from VARCHAR(255) to TEXT:

ALTER TABLE Customers
MODIFY COLUMN email TEXT;

Summary of ALTER TABLE Operations:

1. ADD: Adds a new column to the table.
2. DROP COLUMN: Deletes an existing column from the table.
3. MODIFY COLUMN: Changes the definition of an existing column, such as altering its datatype.

This statement is a versatile tool for maintaining and evolving your table structures as your data model grows or changes.