SQLite Create Table

SQLite'sCREATE TABLEThe statement is used to create a new table in any given database. Creating a basic table involves naming the table, defining the columns, and the data type of 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. The CREATE TABLE statement is followed by the table's unique name or identifier. You may also choose to specify withtable_nameof database_name。

Example

The following 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 in later 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 SQLite command.tablesto verify whether the table has been successfully created. This command is used to list all tables in the attached database.

sqlite>.tables
COMPANY     DEPARTMENT

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

You can use SQLite.schemacommand to get the complete table information, 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
);