MySQL – ALTER TABLE

The ALTER TABLE statement in MySQL is used to modify an existing table’s structure. It allows you to add, modify, or delete columns, change data types, add constraints, rename the table, and more. Here are some examples of how to use the ALTER TABLE statement:

Adding a New Column:

To add a new column to an existing table named “employees,” you can use the ADD keyword along with the new column definition. For example, let’s add a “phone” column of type VARCHAR(20):

ALTER TABLE employees
ADD phone VARCHAR(20);

Modifying an Existing Column:

To modify an existing column, you can use the MODIFY keyword along with the new column definition. Let’s modify the “phone” column’s data type to VARCHAR(15):

ALTER TABLE employees
MODIFY phone VARCHAR(15);

Changing a Column Name:

To change the name of an existing column, you can use the CHANGE keyword. Let’s change the column name “phone” to “contact_number”:

ALTER TABLE employees
CHANGE COLUMN phone contact_number VARCHAR(15);

Deleting a Column:

To delete an existing column, you can use the DROP keyword. Let’s remove the “contact_number” column from the “employees” table:

ALTER TABLE employees
DROP COLUMN contact_number;

Adding a Primary Key:

To add a primary key constraint to an existing table, you can use the ADD keyword along with the PRIMARY KEY constraint. Let’s add a primary key on the “id” column:

ALTER TABLE employees
ADD PRIMARY KEY (id);

Adding a Foreign Key:

To add a foreign key constraint to an existing table, you can use the ADD keyword along with the FOREIGN KEY constraint. Let’s add a foreign key on the “department_id” column referencing the “id” column of the “departments” table:

ALTER TABLE employees
ADD FOREIGN KEY (department_id) REFERENCES departments(id);

These are just a few examples of how you can use the ALTER TABLE statement to modify an existing MySQL table. Always exercise caution when making changes to your database schema, especially in production environments. Ensure that you have appropriate privileges to alter the table and that your changes are consistent with the application’s data requirements.