CREATE TABLE

This SQLite tutorial 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:

1

2

3

4

5

6

CREATE TABLE table_name

(

column1 datatype[NULL|NOTNULL],

column2 datatype[NULL|NOTNULL],

...

);

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.

Examples:

The following CREATE TABLE statement create a table called “employee” that contains four columns:

1

2

3

4

5

6

CREATE TABLE

employee

("id"INTEGERPRIMARY KEY AUTOINCREMENT NOTNULL,

"name"VARCHAR NOTNULL,

"position"VARCHAR,

"hire_date"DATETIME);

The first column is called id 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 name which is a VARCHAR datatype and can not contain NULL values.

The third column is called position 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.

id

name

position

hire_date

The following CREATE TABLE statement create a table called “account” that contains four columns:

1

2

3

4

5

6

CREATE TABLE

account

(id INTEGERPRIMARY KEY AUTOINCREMENT NOTNULL,

salary REAL DEFAULT5000,

account_no INTUNIQUE,emp_id INT,

FOREIGN KEY(emp_id)REFERENCES employee(id));

The first column is called id 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 salary which is a REAL datatype and contain DEFAULT value 5000, if you haven’t specified any, it assumes as 5000.(If you don’t want to specify default value for salary, then you can use CHECK(salary > 0))

The third column is called account_no which is an INT datatype and can contain NULL values and UNIQUE.

The fourth column is called emp_id which is an INT datatype and its a FOREIGN KEY where it only refers to employee id column values.

id

salary

account_no

emp_id

Note: These are the only two table’s, we are going discuss throughout this tutorial.