MySQL ALTER TABLE

Introduction

In this chapter, we will learn how to use the ALTER TABLE statement in MySQL. The ALTER TABLE statement is used to modify an existing table structure. This includes adding, deleting, or modifying columns, as well as changing table properties. We will cover various operations that can be performed using the ALTER TABLE statement.

Adding a Column

To add a new column to an existing table, use the ADD clause.

Syntax

ALTER TABLE table_name ADD column_name datatype constraints;

• table_name: The name of the table you want to modify.
• column_name: The name of the new column.
• datatype: The data type for the new column.
• constraints: Optional constraints for the new column (e.g., NOT NULL, UNIQUE).

Example

ALTER TABLE employees ADD department_id INT;

This example adds a new column named department_id to the employees table.

Dropping a Column

To remove an existing column from a table, use the DROP clause.

Syntax

ALTER TABLE table_name DROP COLUMN column_name;

• table_name: The name of the table you want to modify.
• column_name: The name of the column you want to remove.

Example

ALTER TABLE employees DROP COLUMN department_id;

This example removes the department_id column from the employees table.

Modifying a Column

To modify the definition of an existing column, use the MODIFY clause.

Syntax

ALTER TABLE table_name MODIFY column_name new_datatype new_constraints;

• table_name: The name of the table you want to modify.
• column_name: The name of the column you want to modify.
• new_datatype: The new data type for the column.
• new_constraints: Optional new constraints for the column.

Example

ALTER TABLE employees MODIFY email VARCHAR(150) NOT NULL;

This example changes the email column in the employees table to VARCHAR(150) and adds a NOT NULL constraint.

Renaming a Column

To rename an existing column, use the CHANGE clause.

Syntax

ALTER TABLE table_name CHANGE old_column_name new_column_name datatype constraints;

• table_name: The name of the table you want to modify.
• old_column_name: The current name of the column.
• new_column_name: The new name for the column.
• datatype: The data type for the column.
• constraints: Optional constraints for the column.

Example

ALTER TABLE employees CHANGE first_name firstname VARCHAR(50) NOT NULL;

This example renames the first_name column to firstname in the employees table.

Renaming a Table

To rename a table, use the RENAME TO clause.

Syntax

ALTER TABLE old_table_name RENAME TO new_table_name;

• old_table_name: The current name of the table.
• new_table_name: The new name for the table.

Example

ALTER TABLE employees RENAME TO staff;

This example renames the employees table to staff.

Full Example

Let's go through a full example where we create a table, alter it by adding, modifying, and dropping columns, and then rename the table.

1. Create a Table:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

2. Add a Column:

ALTER TABLE employees ADD department_id INT;

3. Modify a Column:

ALTER TABLE employees MODIFY email VARCHAR(150) NOT NULL;

4. Drop a Column:

ALTER TABLE employees DROP COLUMN department_id;

5. Rename a Column:

ALTER TABLE employees CHANGE first_name firstname VARCHAR(50) NOT NULL;

6. Rename the Table:

ALTER TABLE employees RENAME TO staff;

Viewing the Table Structure

You can view the structure of a table using the DESCRIBE statement to verify changes.

Example

DESCRIBE staff;

Output

Field	Type	Null	Key	Default	Extra
id	INT	NO	PRI	NULL	auto_increment
firstname	VARCHAR(50)	NO		NULL
last_name	VARCHAR(50)	NO		NULL
email	VARCHAR(150)	NO	UNI	NULL
hire_date	DATE	YES		NULL

Important Considerations

• Data Integrity: Ensure that modifying or dropping columns does not lead to data loss or integrity issues.
• Backups: Always back up your data before performing significant alterations on a table.
• Permissions: You need the appropriate privileges to alter a table. Typically, this requires ALTER and DROP privileges.

Conclusion

The ALTER TABLE statement in MySQL is used for modifying the structure of an existing table. This chapter covered how to add, drop, modify, and rename columns, as well as how to rename the table itself.