SQLite Create Table

In this article we will show how to create a table in SQLite database using the CREATE TABLE statement. SQLite allows the creation of a table with different options such as: defining primary key and foreign key, adding uniqueness constraints, NOT NULL, checks, autoincrement, defining column with default value.

Also using SQLite you can create a table using another table, the new table created will have the same columns, with the same name and type as those in the existing table, the new table will be automatically populated with the same records.

SQLite allows the creation of a table based on a SELECT query consisting of one or more tables.

Syntax

SQLite database allows the creation of a new table using the CREATE TABLE statement with the following syntax:

Simple syntax

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);

Complex syntax

CREATE TABLE [IF NOT EXISTS] [schema_name].table_name (
	column_name1 datatype PRIMARY KEY AUTOINCREMENT,
   	column_name2 datatype NOT NULL,
	column_name3 datatype DEFAULT 0,
	column_name4 datatype,
	CONSTRAINT constraint_name UNIQUE (column_name1, column_name2, ...),
	FOREIGN KEY(column_name) REFERENCES another_table_name(column_name),
	CHECK (expr)
) [WITHOUT ROWID];

Let’s explain the above syntax:

Creating a table in the SQLite database begins with the command CREAE TABLE, followed by the name of the table.
The IF NOT EXISTS option checks if the table exists in the SQLlite database. If the table with the given name exists, then it does not create it.
The schema_name option represents the name of the schema in which the table is created. If it is not specified, then the table will be created in the default scheme.

The next step is to specify the table columns: column name, column type and size if applicable, then the options specific to a column such as PRIMARY KEY, the AUTOINCREMENT option applies to number type columns, the FOREIGN KEY constraint to reference a column from another table, the constraint NOT NULL, default value, CHECK sets a constraint on the column.

Each row inserted in a SQLite table contains a special column called “rowid”, which specifies a unique id for each row. If the WITHOUT ROWID option is specified when creating a table, then there will be no “rowid”.

Create table if not exists

Here’s an example of how you can use the CREATE TABLE IF NOT EXISTS statement in SQLite:

CREATE TABLE IF NOT EXISTS cats (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
);

Create table using another table

SQLite create table using another table:

CREATE TABLE customers_bakup 
AS 
SELECT * FROM customers;

Create table from select

SQLite create table using a select query is used to create a new table and populate it with the data returned from a SELECT statement.

CREATE TABLE customers_orders 
AS 
SELECT c.id, c.name, o.order_id, o.amount
FROM customers c, orders o
WHERE c.id=o.customer_id;

SQLite CREATE TABLE with AUTOINCREMENT column:

CREATE TABLE table_name (
column1 datatype AUTOINCREMENT,
column2 datatype,
...
);

SQLite CREATE TABLE with AUTOINCREMENT column example:

CREATE TABLE cats (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
age INTEGER 
);

insert into cats(name, age) values
('Tom', 3), ('Kitty', 5), ('Willow', 6);

SQLite create table with default values:

CREATE TABLE table_name (
column1 datatype DEFAULT value,
column2 datatype DEFAULT value,
...
);

SQLite create table with default values example:

CREATE TABLE cats (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
age INTEGER DEFAULT 0
);

insert into cats(name, age) values
('Tom', 3), ('Kitty', 5), ('Willow', 6);

insert into cats(name) values ('Bella');