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.
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”.