SQLite Create Table

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

Syntax

The following is the basic syntax of the CREATE TABLE statement.

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 unique name or identifier of the table is located after the CREATE TABLE statement. (Optional) You can specifydatabase_nameandtable_name.

Online Example

Below is an example that creates a COMPANY table with ID as the primary key, while NOT NULL is the constraint indicating that these fields cannot be NULL when records are created in this 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 will be used in the exercises in the following 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.tablescommand to verify whether the table has been successfully created, which will be used to list all tables in the additional database.

sqlite>.tables
COMPANY　　DEPARTMENT

Here, you can see the COMPANY table twice, as it displays both the COMPANY table for the main database and the test.COMPANY table created as an alias 'test' for your testDB.db. You can use the following SQLite.schemaCommand to get complete information about the table.

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