1.10. SQLite create table

1.10.1. Grammar ¶

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. The CREATE TABLE statement is followed by the unique name or identity of the table. You can also choose to specify with table_name Of database_name .

1.10.2. Example ¶

The following is an example that creates a COMPANY table with ID as the primary key, and the constraint of NOT NULL means 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, which we will use in the exercises in subsequent chapters:

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

You can use the .tables Command to verify that the table has been created successfully, which is used to list all 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 SQLite .schema Command to get the complete information about the table, as follows:

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
);