SQLite Create Table

The CREATE TABLE statement in SQLite is used to create a new table in any given database. Creating a basic table involves naming the table, defining the columns, and specifying the data type for each column.

Syntax

The basic syntax of the CREATE TABLE statement is as follows:

CREATE TABLE database_name.table_name(
   column1 datatype PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

CREATE TABLE is the keyword that tells the database system to create a new table. Following the CREATE TABLE statement is the unique name or identifier of the table. You can also specify the database_name with the table_name.

Example

Below is an example that creates a COMPANY table with ID as the primary key, and the NOT NULL constraint indicates that these fields cannot be NULL when creating records in the table:

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY NOT NULL,
   NAME TEXT NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(50),
   SALARY REAL
);

Let's create another table that we will use in the exercises of subsequent chapters:

sqlite> CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY NOT NULL,
   DEPT CHAR(50) NOT NULL,
   EMP_ID INT NOT NULL
);

You can verify if the table has been successfully created using the .tables command in SQLite, which lists all the tables in the attached database.

sqlite>.tables
COMPANY     DEPARTMENT

Here, you can see the two tables we just created: COMPANY and DEPARTMENT.

You can use the SQLite .schema command to get the complete information of the table, as shown below:

sqlite>.schema COMPANY
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY NOT NULL,
   NAME TEXT NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(50),
   SALARY REAL
);