To create a table in SQLite, use the CREATE TABLE statement.

This statement accepts the table name, the column names and their definitions, as well as some other options.

Example

Here’s a basic example.

CREATE TABLE Products(
  ProductId INTEGER PRIMARY KEY AUTOINCREMENT, 
  ProductName TEXT NOT NULL,
  Price NUMERIC
);

So in this case, Products is the name of the table, and it contains three columns; ProductId, ProductName, and Price.

In this example, I have added each column’s data type as well as some constraints, but these are all optional.

CREATE TABLE Store.Products(
  ProductId, 
  ProductName,
  Price
);

Data Type is Optional

Yes, you read that right – the data type is actually optional.

SQLite uses dynamic typing and so the data type of a value is associated with the value itself, not with its container (column). This is in contrast to most other SQL database systems, where you must specify the data type when you create the column.

So I could do this instead:

CREATE TABLE Products(
  ProductId, 
  ProductName,
  Price
);

Constraints & Other Options

You have the option of specifying any constraints or other options you’d like to be applied against each column. These include the following:

• DEFAULT clause. This specifies a default value or expression for each column in the table.
• The COLLATE clause to specify the name of a collating sequence to use as the default collation sequence for the column. The default value is BINARY.
• PRIMARY KEY clause. You can optionally specify that a column is a primary key. Both single column and composite (multiple column) primary keys are supported in SQLite.
• SQLite also supports UNIQUE, NOT NULL, CHECK, and FOREIGN KEY constraints.
• A generated column constraint (also called a computed column). These are columns whose values are a function of other columns in the same row.
• Whether the table is a WITHOUT ROWID table. This is a performance optimisation technique that omits the “rowid” column that is a special column that SQLite uses by default. For more information on this technique, see the SQLite documentation.

Temporary Tables

You can specify that a table is a temporary table by using either the TEMP or TEMPORARY keyword.

If using one of these keywords, they must be inserted between the CREATE and TABLE.

Here’s an example:

CREATE TEMP TABLE Products(
  ProductId, 
  ProductName,
  Price
);

You can also add the temp schema if you wish.

See How to Create a Temporary Table for more examples of creating temporary tables in SQLite.

Create a Table from Another Table

You can also use a CREATE TABLE ... AS SELECT statement to create a new table based on another table. When you do this, the new table is populated with the data from the SELECT statement (which selects data from another table or tables).

Here’s a basic example:

CREATE TABLE Store.Products2 AS 
SELECT * FROM Store.Products;

This example creates a new table called Products2 and populates it with all data from the Products table.

All column names are the same as in the original table.

It’s important to note that tables created in this manner have no PRIMARY KEY and no constraints of any kind. Also, the default value of each column is NULL. Also, the default collation sequence for each column of the new table is BINARY.