MySQL Alter Table

Previous Page
Next Page

To change the structure of a table, we use the Alter table syntax.

There are a lot of options that MySQL gives for Alter table. We will add a column, remove a column, change the column's type, and change the foreign key constraint. 

We will see few examples in the command-line client and some examples in the MySQL workbench.

Users require the Alter, Create, and Insert privileges to use the Alter Table command.

Pre-requisites

Command-Line client:

  • Login with user credentials. Users should have Alter, Create, and Insert privileges for the database.
  • Choose the database using the USE statement.

Workbench:

  • Initiate a new connection with user credentials. Users should have Alter, Create, and Insert privileges for the database.
  • Open a new Script tab.
  • Choose a database using the USE statement.

MySQL Alter table Syntax

  • To add a column to a table, the syntax is:
ALTER TABLE <table-name> ADD <column-name> <data type> <any conditions>

Add a new column to a table in MySQL using the command line

Example:

To add the column salary to the EMPLOYEES table in the somedb database.

ALTER TABLE EMPLOYEES ADD COLUMN SALARY VARCHAR(40);

Modify an existing column to a table in MySQL using the command line

  • To modify a current column of a table, the syntax is:
ALTER TABLE <table-name> MODIFY <column-name> <data type/condition>

Example:

To modify a column in a table "ratings," we do:

ALTER TABLE RATINGS MODIFY PERCENTAGE FLOAT; 

Add a foreign key to a table in MySQL using MySQL Workbench

  • To add a foreign key to a table
ALTER TABLE <table-name> ADD FOREIGN KEY (<column-name>) REFERENCES <other table-name> (<column_name>)

Example:

Add the foreign key to the employees table

ALTER TABLE EMPLOYEES
ADD FOREIGN KEY (rating) REFERENCES ratings(rating_id);

Drop a column in MySQL using MySQL workbench

  • To drop a column, the syntax is:
ALTER TABLE <table-name> DROP COLUMN <column-name>;

Example:

To drop a column in a table.

ALTER TABLE EMPLOYEES DROP COLUMN PERFORMANCE; 

Previous Page
Next Page