MySQL ADD COLUMN IF NOT EXISTS

This article will discuss the script to add a column to a MySQL table only if it does not already exist.

Let us get started by making the sample table using the create table script.

CREATE TABLE IF NOT EXISTS sale_details (
    id INT auto_increment,
    sale_person_name VARCHAR(255),
    no_products_sold INT,
    sales_department VARCHAR(255),
    primary key (id)
);

Action Output Message:–

0 row(s) affected

To view the columns of the table, let us execute:

DESC sale_details;

Action Output Message:–

4 row(s) returned

Output:-

Currently, the table has only four columns. We will now try to add the column named sale_person_designation with VARCHAR datatype to the sale_details table only after checking if the column already exists or not.

DELIMITER $$
DROP PROCEDURE IF EXISTS addColumnToTable $$
CREATE PROCEDURE addColumnToTable()
BEGIN
IF 
NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
        AND COLUMN_NAME='sale_person_designation' AND TABLE_NAME='sale_details') ) 
THEN
    ALTER TABLE sale_details ADD sale_person_designation varchar(255);
END IF;
END $$
CALL addColumnToTable() $$
DELIMITER ;

Action Output:–

Procedure addColumnToTable is created and called successfully.

Explanation:-

• The above script is creating a procedure named addColumnToTable.
• IF checks whether the column sale_person_designation already exists in the table sale_details within the NOT EXISTS() function. The information if the column sale_person_designation is present in the table sale_details is retrieved from information_schema.
• Database() function retrieves the current schema from the MySQL server.
• In case the IF condition is not true, the statement after THEN is executed. ALTER TABLE statement will ADD the new column to the table.
• CALL addColumnToTable() is executed whenever we want to run the procedure addColumnToTable.

We will again execute the DESC sale_details statement to verify if the column got added or not.

DESC sale_details;

Action Output Message:–

4 row(s) returned

Output:-

Output in image_4 shows the new column sale_person_designation in the table is added.

What if the column already exists in the table sale_details? Let us call the procedure addColumnToTable() again.

CALL addColumnToTable();

Action Output Message:–

0 row(s) affected

ACTION OUTPUT:-

No error is thrown by the MySQL server, which shows that the condition has been checked successfully before calling the ALTER TABLE statement. To verify the same, we will try to ADD the same column without checking the condition.

ALTER TABLE sale_details ADD sale_person_designation VARCHAR(255);

Action Output Message:–

Error Code: 1060. Duplicate column name ‘sale_person_designation’

Action Output:–

READ MORE:

• MySQL IF EXISTS DROP Table
• Insert into a MySQL table or update if exists
• MySQL create table if not exists

We hope this article helped with adding a column in the table if it does not already exist. Good Luck!!!