CREATE TABLE


 

SQLite: CREATE TABLE Statement

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:

 

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:

    • 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:

    • 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.