How to Create MySQL Table if Not Exists (Statement)
How to Create MySQL Table if Not Exists (Statement). MySQL, one of the most extensively used database solutions worldwide, is the most popular open source database.
Currently used extensively and productively in many sectors of the economy.
It manages data stored in numerous tables. There are rows and columns in every table, each corresponding to a record and each column to a field.
Well, MySQL database creates new tables using the CREATE TABLE query. After creating a table based on the particular CREATE TABLE command, the function returns the number of rows impacted by the action.
But sometimes, the table already exists, which causes this command or statement to fail. This article demonstrates using the MySQL CREATE TABLE statement with the IF NOT EXISTS clause to resolve this issue.
What is MySQL?
All in all, MySQL is the most extensively used open source relational database management system (DBMS) that serves many databases to multiple users.
Interestingly, MySQL has been powering online, mobile, and embedded apps for over 20 years. Great web applications, big data and analytics, IoT, in-memory caching, distributed computing, and more use it for data management.
MySQL enables even non relational system experts to swiftly build dependable, quick, and secure data storage systems.
Exists and Not Exists Operator in MySQL
The IF EXISTS and IF NOT EXISTS clauses determine whether a table exists before executing an action on it.
The IF EXISTS clause is usually used to see, if a table exists before attempting to create it. Nothing occurs if the table exists. If it does not already exist, the statement makes it so.
Similarly, the IF NOT EXISTS clause determines whether a table exists before attempting to drop it. Nothing occurs if the table exists. If it doesn’t exist, the statement skips over it.
Note that MySQL queries that employ the EXISTS and NOT EXISTS conditions are usually inefficient since the sub-query is run for every entry in the outer query. But it is only ever useful for the tables under a particular database, as the number of tables is much more limited.
Follow this post to learn how to create MySQL tables with the IF NOT EXISTS statement.
How to Create MySQL Table if Not Exists (Statement)
To compare and contrast the two methods and approaches, we present examples for creating tables with and without the IF NOT EXISTS clause. The examples also cover the dropping table and the IF EXISTS clause.
The examples cover every possible case and situation. So that you understand the working of the IF NOT EXISTS and IF EXISTS clauses and how they affect the execution of the create table and drop table commands.
Basic Syntax of Using the If Not Exists Statement
As shown below, you use the IF NOT EXISTS clause in the CREATE TABLE command.
CREATE TABLE IF NOT EXISTS table_name(
column_definition1,
column_definition2,
........,
table_constraints
);
Note that the IF NOT EXISTS clause runs a check on the name of the table. It will not take note of the attributes of the table. If you create a table with the same attributes but a different table name, the IF NOT EXISTS clause won’t affect it, and table creation will be successful.
Table Creation With and Without the If Not Exists Clause
First of all, log in to MySQL with the following command:
sudo mysql -u username
Next, use the following commands to enter your database and display tables:
USE movies;
show tables;
Note the movies table in the database. Run the following command to recreate this table without the IF NOT EXISTS clause:
CREATE TABLE movies(title VARCHAR(50) NOT NULL,genre VARCHAR(30) NOT NULL,director VARCHAR(60) NOT NULL,release_year INT NOT NULL,PRIMARY KEY(title));
You will see that the command will fail due to the error that the table already exists. Run the following command to see the error in detail:
SHOW WARNINGS;
Next, run the same command with the IF NOT EXISTS clause to see the difference in the output.
CREATE TABLE IF NOT EXISTS movies(title VARCHAR(50) NOT NULL,genre VARCHAR(30) NOT NULL,director VARCHAR(60) NOT NULL,release_year INT NOT NULL,PRIMARY KEY(title));
You see that the output ran smoothly without any errors, but there is a warning. The details of the warning with the help of the following command:
SHOW WARNINGS;
The warning shows that the table called movies already exists.
The difference in both cases is that the condition is checked first with the IF NOT EXISTS, operator. If it does not match, the table creation is skipped with the warning.
Without the IF NOT EXISTS clause, the system attempts to create the table but fails and generates an error.
Create a new table with the same clause with the following command.
CREATE TABLE IF NOT EXISTS TEST (
c1 INT,
c2 VARCHAR(10)
);
You see the query has been executed with no warning.
Display the tables in the database with the following command.
show tables;
When the new table name appears in the list, it means that the system has successfully created the table.
Check if the Table Already Exists
Use an alternate method to avoid errors while creating tables. The method is to check if the table exists before running the CREATE TABLE command.
Check the table’s existence in two ways. The first is to display tables and check them by comparison.
Alternatively, run the following commands to check if the table already exists.
CALL sys.table_exists('movies', 'movies', @table_type);
SELECT @table_type;
The table already exists in the database, if you see the BASE TABLE under the @tabletype in the output.
But if you see a blank space under the @tabletype, this table is not in the database, and you go ahead with the table creation.
Drop Table With and Without the If Exists Clause
Similarly, the IF EXISTS clause plays the same role in table deletion as the IF NOT EXISTS clause does in table creation.
The IF EXISTS clause ensures that the table about to be deleted exists in the database.
First, run the DROP TABLE command without the IF EXISTS clause for a non existing table with the following command.
drop table gp;
You see the command fails with the error message that you are trying to drop an unknown table in the output.
Next, run the same command with the IF EXISTS clause.
drop table IF EXISTS gp;
See the command executes successfully, but there is a warning in the output.
Use the following command to see the details of the warning:
SHOW WARNINGS;
You notice the warning that the table doesn’t exist. Which means that the condition needed for performing the drop table command does not meet so the system skips the DROP TABLE command.
When you execute the same command for a existing table in the database, you will observe that the command succeeds and deletes the table.
drop table IF EXISTS TEST;
That’s it for how to create a MySQL table with the IF NOT EXISTS clause and drop a table with the IF EXISTS operator.
How to Create MySQL Table if Not Exists (Statement) Conclusion
After establishing tables in MySQL, you use the clauses IF NOT EXISTS and IF EXISTS to avoid systematic errors. You have learned how and where to use these clauses in this article.
Using the IF NOT EXISTS clause with the CREATE TABLE statement would execute the command correctly and construct a table only if there is no other table with a similar name in the database.
Similarly, the IF EXISTS clause with the DROP TABLE statement provides successful output.
Explore our MySQL section to learn more.
