MySQL create table if not exists

This article will discuss the script to create a table in MySQL only if it does not already exist. We will be using the IF NOT EXISTS clause within the create table script.

Further, in the examples, we will be writing create table scripts using the IF NOT EXISTS clause and without it to analyze the difference between both.

Let us get started by creating the table. We are assuming that this is a fresh creation.

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:-

Output Message in image_1 shows that table is created successfully.

Let us re-run the create table statement.

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:-

The output in image_2 shows a warning with the below message response.

Action Output Message:-

0 row(s) affected, 1 warning(s): 1050 Table ‘sale_details’ already exists 0.0021 sec

MySQL server has not returned with an error in this case, but what if the IF NOT EXISTS clause is absent in the create table statement. Let us view the situation by removing the IF NOT EXISTS clause and running the statement to create the table again.

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

Action Output:-

The highlighted row in the image_3 shows the MySQL server threw an error, this time with the below message.

Action Output Message:-

Error Code: 1050. Table ‘sale_details’ already exists.

READ MORE:

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

We hope this article helped with IF NOT EXISTS CREATE TABLE statements. Good Luck!!!