Home > DeveloperSection > Blogs > SQLite - CREATE Table

SQLite - CREATE Table


SQLite Sqlite  Sqlite3 
Ratings:
0 Comment(s)
 713  View(s)
Rate this:

SQLite - CREATE Table    


In this article we are going to discuss about creating table using SQLite CREATE TABLE statement that is used to create a new table in any of the given database. Creating a basic table involves naming the table and defining its columns and each column's data type.


Syntax:


Basic syntax of 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 to create a new table and unique name or identifier for the table follows the CREATE TABLE statement. Optionally we can specify database_name along with table_name.

 

Example:


Following is an example which creates a CONTACT table with ID as primary key and NOT NULL are the constraints showing that these fields can’t be NULL while creating records 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

            );

 

Now, let us create one more table:


sqlite> CREATE TABLE DEPARTMENT(

                ID   INT PRIMARY KEY    NOT NULL,

                DEPT             CHAR(50)   NOT NULL,

                EMP_ID        INT              NOT NULL

            );

 

We can verify if our table has been created successfully using SQLIte command .tables command, which will be used to list down all the tables in an attached database.

sqlite>.tables

COMPANY     DEPARTMENT

Here, we can see COMPANY table twice because its showing COMPANY table for main database and test.COMPANY table for 'test' alias created for our testDB.db. We can get complete information about a table using SQLite .schema command 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

);

 


Don't want to miss updates? Please click the below button!

Follow MindStick