How to Create Table in SQLite Using “if not exists” Statement?

SQLite is a serverless RDBMS, which is used to manage data in the database in the form of tables. These tables are created in the database to store data in columns and rows, for this purpose, tables can either be created using the “CREATE TABLE” or the “CREATE TABLE if not exists” statement in SQLite.The “CREATE TABLE if not exists” statement is very useful in creating a table because it will not create the table if the table of the same name already exists in the database. In this writeup, we will explain how the “CREATE TABLE if not exists” works, and what happens if we try to create a table without using the “CREATE TABLE if not exists”.

How to Create a table without using “if not exists” in SQLite

First, we will open the terminal in SQLite and will create a table, LinuxHint_employees.

To confirm the creation of the table, we will display the tables:

The table, LinuxHint_employees,  has been created. Now, we will create another table of the same name, LinuxHint_employees, by using the create statement:

On execution of the statement in a terminal, it generated the error of “Error: table LinuxHint_employees already exists”. This error occurs because another table with the same name “LinuxHint_employees” already existed.

How to create a table using “if not exists” in SQLite

The “if not exists” statement used in the creation of the table,  first will analyze the list of all tables present in that schema, then if there is no table with the name of the table,  which is going to be created, it will successfully create the table, else it will execute the command successfully without creating the table as well generating the error of “table already exists”. We will explain it with the help of an example but before explaining the example, let us explain the general syntax of using the “if not exists” clause for the creation of a table. The general syntax of creating a table using the “if not exists” statement:

The explanation of this syntax is as:

• Use the clause “CREATE TABLE” to create a table
• Write the clause “if not exists”
• Write the table name instead of table_name
• Write the column_name
• Declare the datatype, which type of data will be inserted in the column

We will execute the same command using “if not exists”, which will not  generate the error of “table already exists” as:

The command has run successfully without generating the error of “table already exists” because in SQLite it is usually not an error to create a table with the same name. To confirm that either it has created another table with the same not or not, we will display the list of tables:

So it has not created the table with the same name as well, now we will create a table, named, “JohnCompany” using the if not exists statement:

To display the list of tables:

The table has been created because there is no table that exists with the same name in the schema.

Conclusion

SQLite has a lightweight architecture, as it has no server which means it is using the operating system of the machine in which it has been operating instead of having its own separate server. In SQLite, creating a table is so essential because in it you are defining the columns and rows in which data has to be stored. SQLite, like other DBMS, contains a variety of built-in clauses which are used to run the statements easily. In this writeup, we have explained what the “if not exists” clause is and how it is used for the creation of the table. This clause first verifies if there is another table with the same name or not, if it does not exist then it will create that table, else simply run the command without generating the error of “already exists”.