SQLite – CREATE TABLE Statement

In SQLite the CREATE TABLE Statement can be used to create table in a database.

A database in SQLite can contain one or more tables. The table conatins one or more rows as well as one or more columns.

Each table in SQLite must have a name.

SQLite CREATE TABLE Syntex

The syntex for creating a new table in a database using SQLite is show below:

CREATE TABLE table_name
(
column_name1 data_type PRIMARY KEY(can have one or more columns),
column_name2 data_type,
column_name3 data_type,
....
column_nameN data_type,
);

---

Example:

So let’s consider we have created SCHOOL database. Now we want to create a STUDENTS table in this database.

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

In the Example above we created a STUDENTS table with five columns (ID, NAME, SURNAME, AGE, ADDRESS). The table STUDENTS have ID column as primary key and NOT NULL. These are the constrains to the field ID so that field ID can not be NULL while creating records.

Now Lets say we want to create one more table in SCHOOL database. This time we will create a table called TEACHERS.

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

---

.tables command

Above we have created two tables in the database. .tables command can be used to list down all the tables in the database.

sqlite>.tables
STUDENTS     TEACHERS

.schema command

.schema command can be used to view the complete information or the table.

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

---