SQLite CREATE TABLE Statement

SQLite CREATE TABLE Statement

The SQLite CREATE TABLE statement is used to create a new table in the database. Before creating the table, we must design and decide basic table details and relationships with other tables. The ER diagram explains these details.

 

Basic table details involve:

• Table Name
• Column definitions and each column’s data type.
• Table Primary key and Foreign keys
• Table Constraints.

SQLite CREATE TABLE

The SQLite create table general syntax is as follows:

CREATE TABLE IF NOT EXISTS database_name.table_name(
column1 datatype PRIMARY KEY,
column2 datatype constraints,
column3 datatype,
…..
columnN datatype,
FOREIGN KEY( this_table_column_name) REFERENCES another_table(another_table_column)
);

 

The IF NOT EXISTS clause is used to prevent errors that may occur if you try to create a table or index that already exists in the database. The table will be created if it does not exist; otherwise, the statement will have no effect.

Example

Let’s create a table with the following columns.

CREATE TABLE emp (
empno INTEGER PRIMARY KEY,
ename TEXT NOT NULL,
jobtitle TEXT NOT NULL,
salary INTEGER NOT NULL
);

 

This statement creates a table with the specified columns and data types if the table does not already exist. If the table already exists, no error is thrown.

The .tables command displays the tables in the database.

/> .tables

Once the table is created, we can run simple SQL queries to insert or populate the table and view the data. We can use the INSERT statement to populate data into the table.

Create SQLite Table

In this tutorial, we will learn how to create an SQLite table. DB Browser for SQLite database tool lets you create tables.

SQLite Table

To create a table, we need to know its schema, which includes the number of columns, data type of each column, default values, table constraints, Primary key, foreign key, etc.

The two main ways to create the SQLite table are as follows:

• Use the SQL CREATE table query on the command line.
• Use the DB Browser GUI Tool.

This section will create the table using the DB Browser tool. Follow the below steps to create a table.

Launch the DB Browser tool.

To create a table, open the database where you want to create it.

To create a table, click on the Create Table tab. This will open the table definition window, where you can enter the details of the table you wish to create.

 

Click the Add button to add the columns to the table definition.

Click on OK after you are done with the table specifications.

Once you have added the columns, click the OK button, and the table should appear under the database you created.

 

—

SQLite Tutorials

SQLite tutorials on this website:

https://www.testingdocs.com/sqlite-database-tutorials/

For more information on SQLite, visit the official website:

https://www.sqlite.org/