How to Create Table in SQLite?

SQLite is a relational database management system (RDBMS), which is open-source and used to manage data of databases in the form of tables, which are further divided into rows and columns. Tables are created to manage data of the databases, for the creation of tables there is a query to be executed about which we can discuss in the next section.

SQLite is serverless and very efficient for databases whose sizes are up to 140 TB. In this write-up, we will discuss the creation of a table in SQLite with the help of the different types of examples.

How to create a table in SQLite?

As we define that SQLite manages data in the form of tables, so before inserting the data in the table we have to create the tables. For creating the table in SQLite, we use the “CREATE TABLE” statement. Before understanding how this statement works, let us discuss the general syntax of the creation of the table.

The general syntax of creation of table in SQLite:

CREATE TABLE [IF NOT EXISTS] [TABLE_NAME] (column1 datatype [one OR more COLUMNS]);

In the syntax above, the statement of “CREATE TABLE” is used for the creation of the table :

The “if not exists” clause can be used while creating a table. The table will not be created if there is any table with the same name that already exists.
Along with database_name, after putting dot “.”, the table_name should be assigned
Column title along with its data type should be mentioned
If there is any primary key, mentioned it as describe in the above syntax or else leave it

Let us consider an example of creating a table, we will create a table of LinuxHint_employees using the “CREATE TABLE” statement. Run the command of “sqlite3” in the terminal, and open the SQLite with the database name “Organization”.

$ sqlite3 Organization.db

To create a table with the table constraints (employees_id and employees_name) of data types (int and string) with the name of LinuxHint_employees, execute the following command:

CREATE TABLE LinuxHint_employees (employees_id INT, employees_name TEXT);

Create another table using the “if already exists” clause with a name of John_employees and constraints employees_name and employees_id using the employees_id as PRIMARY_KEY:

CREATE TABLE IF NOT EXISTS John_employees (employees_id INT PRIMARY KEY, employees_name TEXT);

Both tables are created successfully. To display these tables, execute the command:

.tables

Consider some more examples of the creation of the tables using “temp” and “temporary” clauses. First, we will create a temporary table using “temp” as:

CREATE TEMP TABLE IF NOT EXISTS Maadi_employees (employees_id INT PRIMARY KEY, employees_name TEXT);

Successful execution of command means a temporary table has been created with the name, Maadi_employees. Similarly, we can create a temporary table using the “TEMPORARY” keyword as:

CREATE TEMPORARY TABLE IF NOT EXISTS Linux_employees (employees_id INT PRIMARY KEY, employees_name TEXT);

To confirm that tables have been created we will display the tables using the command:

.tables

The output shows, temporary tables have been created and are present in the database with “temp”.

Conclusion

SQLite is a serverless relational database management system that manages the data in the form of tables, where tables are objects that hold the data in rows and columns and each column has a specific data type. In this writeup, we have explained with different examples how to create a table in SQLite. Tables can be created in SQLite using the “CREATE TABLE” statement to manage the data of a database. We have created tables with and without using the PRIMARY KEY, and also created the temporary table by using both “TEMP”, and “TEMPORARY” clauses.

How to Create Table in SQLite?

How to create a table in SQLite?

Conclusion

About the author

Hammad Zahid