MySQL ALTER TABLE – How To Add Column To A Table In MySQL

Learn about MySQL ALTER Table command to add/drop a column, index, constraint, change table name, etc. with examples:

MySQL ALTER command is used to modify an existing table by adding a new column or removing an existing column or changing the data type of column.

Simply speaking, the ALTER command is used to modify the structure of an existing table by adding/removing/updating columns, renaming tables, etc.

It’s almost impossible that all the requirements for a given schema are available beforehand, hence it is imperative that there should be a way to update the table structure as needed.

=> Click here for the complete MySQL tutorial series

MySQL ALTER TABLE

SYNTAX:

ALTER TABLE table_name [alter_option1, alter_option2...]

Here table_name is the name of the table on which we want to perform the alter action. One or more alter_options can be specified along with the ALTER TABLE command.

Let’s look at different majorly used alter_options provided by MySQL.

Sample Data and Table Setup

For the purpose of understanding different usages of the ALTER command, we will use 2 sample tables – Employee and Department having the structure as mentioned below.

We are also adding some dummy data to both the tables.

We haven’t added any FOREIGN KEY constraints among these tables. We will learn to add them using the ALTER command in the examples shown in the below sections as we discuss.

CREATE TABLE IF NOT EXISTS Employee_Department (
    id INT, 
    name VARCHAR(100),
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS Employee (
    name VARCHAR(100),
    id INT,
    address VARCHAR(100),
    department_id INT,
    PRIMARY KEY (id)
);

INSERT INTO `employee_department`(`name`,`id`) VALUES ('Information 
Technology',1),('HR',2),('Finance',3),('Accounting',4),('Housekeeping',5
),('Security',6),('Support',7),('Contract Staff',8),('Sales',9),('Management',10); 

INSERT INTO `employee` (`id`,`name`,`address`) VALUES (1,"Alyssa 
Villarreal","Ap #558-3012 Nulla Street"),(2,"Vanna Parks","103-986
 Cursus Rd."),(3,"Quyn Byers","770 Nulla Avenue"),(4,"Kristen Hall","Ap 
#622-9967 Nullam St."),(5,"Chelsea Stone","6017 Elementum 
St."),(6,"Sacha Sweeney","239-7402 Etiam St."),(7,"Sophia Charles","799
-745 Tellus. Rd."),(8,"Jennifer Joyner","5803 Ligula.
 Avenue"),(9,"Mercedes Head","Ap #375-7999 Facilisis Rd."),(10,"Reagan Holland","P.O. Box 625, 5744 Proin Avenue");

Different Ways To Use ALTER Table Command

The MySQL ALTER Command can be used for multiple things like add/drop a column, add/drop index or constraint, and update a table itself. Let’s look at different scenarios with the help of examples.

ADD COLUMN

With the above sample tables, let’s add a new column named ‘department_id’ of type INT in the table Employee.

ALTER TABLE employee ADD COLUMN department_id INT

DROP COLUMN

Similar to adding a new column, the MySQL ALTER command can be used to drop an existing column from the table.

Try to remove the above-added column department_id from the Employee table.

ALTER TABLE employee DROP COLUMN department_id

Please note that, while dropping a COLUMN, you just need to mention the column name and not its data type.

To validate if the ALTER command succeeded, you can run the SHOW COLUMNS command.

SHOW COLUMNS FROM employee;

Output:

You can see there’s no department_id column in the ‘Employee’ table now.

Add/Drop Constraints

Let’s now see how ALTER can be used to add a constraint.

ALTER command can be used to add or remove constraints for an existing table. Let’s see an example of a FOREIGN KEY Constraint that needs to be added between 2 tables.

We will use the ALTER command to add FOREIGN KEY CONSTRAINT in the Employee table for the employee_department table.

Add a new column named department_id in the Employee table.

ALTER TABLE employee ADD COLUMN department_id INT

Now, add the FOREIGN KEY constraint.

ALTER TABLE employee ADD FOREIGN KEY (department_id) REFERENCES employee_department(id)

To validate this constraint, we can use the SHOW command to see the table definition.

SHOW CREATE TABLE employee;

You will find that there’s a FOREIGN KEY constraint created on the department_id column.

Let’s now see how we can use the ALTER command to remove the FOREIGN KEY constraint that we just added.

ALTER TABLE employee DROP CONSTRAINT employee_ibfk_1

Please note that you will need to use the name of the constraint that got created. So in the above SHOW command output, you can see the constraint name that MySQL has created for the FOREIGN KEY constraint is ‘employee_ibfk_1’

Run the SHOW command to see if the constraint is removed as shown below:

SHOW CREATE TABLE employee;

Add/Drop Indexes

MySQL ALTER table command also allows you to create or drop INDEXES against a table.

There can be four different types of indexes that can be added using the ALTER TABLE command.

#1) Add PRIMARY KEY: This command adds a PRIMARY KEY index against a given column (or columns)
In the below example, use the Employee table and add the PRIMARY KEY Index to the ‘Id’ column.

ALTER TABLE employee ADD PRIMARY KEY (id);

#2) Add UNIQUE: Adds or creates Unique index against given column(s)
Suppose hypothetically we just want unique names in a table. Add a UNIQUE index on the ‘name’ column in the Employee table as shown below.

ALTER TABLE employee ADD UNIQUE (name);

#3) Add INDEX: Add an ordinary index against any column.
Add a normal index on the ‘address’ field in the Employee table.

ALTER TABLE employee ADD INDEX (name);

#4) Add FULLTEXT Index: This is a special type of Index which is used for text searching purposes.
We will be adding a FULLTEXT Index on the ‘name’ column values in the Employee table.

ALTER TABLE employee ADD FULLTEXT(address);

For all the above 4 examples, we can run the SHOW CREATE TABLE command to get the details about the indexes created.

SHOW CREATE TABLE employee;

In the above output, you will observe that all the indexes have been created as per the value in the create_table column.

Similar to Creating Indexes, the ALTER command can also be used to drop existing indexes from the table.

Example:

ALTER TABLE employee DROP INDEX address;

The above command will DROP the FULLTEXT Index that we have added for the address field.

Notice the name ‘address’. This should match the index name in the output of the SHOW CREATE TABLE command.

Change Default Value Of Column

ALTER TABLE command can also be used to SET/Change the DEFAULT value for an existing column.

Let’s look at an example where we will change the default value of the department_id column to ‘1’. With this, any new inserts to this table would change/set the value for the department_id column to 1

ALTER TABLE employee ALTER department_id SET DEFAULT 1

Add a new record to the Employee table:

INSERT INTO `employee` (`id`,`name`,`address`) VALUES (11,"Saket Sharma","NB 23 Domlur")

And now let’s query the department_id for this record and see what’s the value being set.

SELECT department_id FROM employee where id=11

//Output
department_id
1

Update Table Name

MySQL ALTER table command can also be used to update the name of an existing table. At times it is required to update schema structure, which may involve changing/updating the table names.

Example – We will rename the table `employee` to `Employee_details`.

ALTER TABLE table_name RENAME TO new_table_name;

Frequently Asked Questions

Q #1) How do I change table structure in MySQL?

Answer: MySQL provides the ALTER command to change the structure of an existing table in MySQL. You can perform various operations with ALTER command like,

• Adding/Dropping a column
• Adding/Removing Index or Constraint
• Renaming a table

Q #2) Does ALTER command lock a table?

Answer: It might depend on versions – with older versions of MySQL Locking a table during the execution of the ALTER statement. In general, the locking would happen i.e. any read and writes during ALTER.

For example – Adding a new column for an existing table would touch all the rows of the table in which column is being added and when the table has number of records in millions, the ALTER operation might take some time to execute. In this case, the Locking happening on the reads/writes would be costly as the database would be down and inaccessible.

Q #3) Can we add more than one column in the ALTER table?

Answer: Yes, the ALTER command can be used to add multiple columns within a single statement.
Let’s look at an example where we have a table Employee with columns id, name, and address and suppose we want to add 2 more columns named – highest_education (type varchar) and phone_number (type varchar)

ALTER TABLE employee
ADD highest_education VARCHAR(50), 
ADD phone_number VARCHAR(20);

Let’s have a look at the table info

SHOW COLUMNS FROM employee;

Output:

In the output above you can see the columns, highest_education and phone_number have got added.

Conclusion

In this tutorial, we learned about different usages of the MySQL ALTER Table Command. ALTER is an important command as it can be used for multiple purposes to ALTER the structure of an existing table in MySQL.

It can be used for things like – adding/removing a column, adding/dropping Indexes or Constraints, and can also be used even to rename an existing table.

Anyone learning MySQL should have an in-depth understanding of the ALTER table command, as it’s mostly used during maintenance phases of databases where existing table schema is required to be altered.