SQLite: CREATE TABLE Statement

This SQLite post explains how to use the SQLite CREATE TABLE statement with syntax and examples.

Description

The SQLite CREATE TABLE statement allows you to create and define a table.

Syntax

The syntax for the CREATE TABLE statement in SQLite is:

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

Parameters or Arguments

table_name

The name of the table that you wish to create.

column1, column2

The columns that you wish to create in the table.

datatype

The data type for the column.

Note

• There can only be one column in a table that is set as AUTOINCREMENT with a datatype of INTEGER. This column must be the primary key.

Example

Let's look at a SQLite CREATE TABLE example.

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE
);

This SQLite CREATE TABLE example creates a table called employees which has 4 columns and one primary key:

• The first column is called employee which is created as an INTEGER datatype. It has been defined as the primary key and is set as an AUTOINCREMENT field which means that it is an autonumber field (starting at 1, and incrementing by 1, unless otherwise specified.)
• The second column is called last_name which is a VARCHAR datatype and can not contain NULL values.
• The third column is called first_name which is a VARCHAR datatype and can contain NULL values.
• The fourth column is called hire_date which is a DATE datatype and can contain NULL values.

Next, let's create a table that has a DEFAULT VALUE.

CREATE TABLE products
( product_id INTEGER PRIMARY KEY AUTOINCREMENT,
  product_name VARCHAR NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 0
);

This SQLite CREATE TABLE example creates a table called products which has 3 columns and one primary key:

• The first column is called product_id which is created as an INTEGER datatype. It has been defined as the primary key and is set as an AUTOINCREMENT field.
• The second column is called product_name which is a VARCHAR datatype.
• The third column is called quantity which is an INTEGER datatype and can not contain NULL values. If no value is provided for this column, the DEFAULT VALUE will be 0.