SQLiteDB – Create Table:

In SQLite, you can create a new table using the command CREATE TABLE. Please find the syntax as below.

Syntax:

CREATE TABLE [IF NOT EXISTS] [schema_name].table_name (

 column_1 data_type PRIMARY KEY,

   column_2 data_type NOT NULL,

 column_3 data_type DEFAULT 0,

table_constraint

) [WITHOUT ROWID];

Example:

CREATE TABLE CUSTOMERS (

cust_id integer PRIMARY KEY,

first_name text not null,

last_name  text not null,

Gender  text not null,

email  text not null uniQUE,

City   text not null

);

Let’s create a table called customers in the SQLite Database customer.

sqlite> .databases

seq  name             file                                                      

---  ---------------  ----------------------

0    main                                                                      

2    customer         /root/custDB.db



sqlite> CREATE TABLE CUSTOMERS (

   ...>cust_id integer PRIMARY KEY,

   ...>first_name text not null,

   ...>last_name  text not null,

   ...>Gender  text not null,

   ...>emailtext not null uniQUE,

   ...> City   text not null

   ...> );

Verify the table created in Database customer.

sqlite> .tables

CUSTOMERS

Use the below command to see the structure or complete command used to create the table.

Syntax: .schema [table_name]

sqlite> .schema customers

CREATE TABLE CUSTOMERS (

cust_id integer PRIMARY KEY,

first_name text not null,

last_name  text not null,

Gender  text not null,

Emailtext not null UNIQUE,

City   text not null

);

sqlite>