MySQL MariaDB

How to create table in MySQL using “if not exists” technique

Every database is a collection of tables containing the data so after the creation of a database, tables are to be created to add data. The clause “if not exists” is used for the creation of tables and is very useful for avoiding the error “table already exists”, as it will not create a table if, in the database, any table is already available by the name of the new table.

We will understand more about the use of the clause “if not exists” for the creation of tables in this article with the help of different examples.

How we can create a table using the “if not exists” technique

We will first open MySQL in the terminal:

$ sudo mysql

Show the databases we have:

A list of all the databases will be displayed, we will use shopping_mart_data.

USE shopping_mart_data;

To list down the tables of this database, we will run the following command.

We have a table with the name “Gocery_bill”, we will first try to create a table with the same name.

CREATE TABLE Grocery_bill (Employee_Id INT, Employee_name VARCHAR(50));

We can see an error has been generated that “Table ‘Grocery_bill’ already exists”, now we will run the above command by using the “if not exists”.

CREATE TABLE if not exists Grocery_bill (Employee_Id INT, Employee_name VARCHAR(50));

The command runs successfully but we discussed above that the two tables with the same name cannot be created, so we will again display the tables of the database to verify whether another table is created or not.

It is so confirmed that with the same name two tables cannot be created and if we use “if not exists”, it will run the command successfully without generating the error but will not create the table that is already a table that exists with the same name. Now we will create the table using again the clause “if not exists” with a different name.

CREATE TABLE if not exists Employee_data (Employee_Id INT, Employee_name VARCHAR(50));

A new table has been created successfully to verify this again display the tables of the database.

The table has been created because there is no table that already exists with the name “Employee_data”.

Conclusion

MySQL is used by many companies like Amazon and Twitter for its features of high efficiency and different build-in clauses which can ensure the tasks to be fulfilled with ease. In MySQL all the data is managed in tables after creating them, to avoid the error of the same table already existing we use the clause “if not exists” in the statement to create the table. In this article, we have discussed if the clause “if not exists” is used with the “CREATE TABLE ” statement then it will execute the command successfully without generating an error and create a table only if there is no other table of similar name present in the database.

About the author

Hammad Zahid

I'm an Engineering graduate and my passion for IT has brought me to Linux. Now here I'm learning and sharing my knowledge with the world.