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