SQLite Create, Alter, Drop Table

Details: Last Updated: 18 October 2018

In this article, we will see how to create tables, modify tables and dropping tables in SQLite. But the first thing is to open the database that we will work on as we have shown you before.

In this tutorial, you will learn-

Create table
Drop table
Alter table
Add columns- Using ALTER TABLE Command
Insert value into a table

SQLite Create table

To create a table, you should use the "CREATE TABLE" command as following:

CREATE TABLE guru99 (
  Id Int,
  Name Varchar
);

Within the two brackets after the table name, you define the tables' columns, each column should have the following properties:

A name, the column name it should be unique among the table's columns.
A data type – the column data type,
Optional column constraints as we will explain in the later sections in this tutorials.

Drop table

To drop a table, use the "DROP TABLE" command followed by the table name as following:

DROP TABLE guru99;

Alter table

You can use "ALTER TABLE" command to rename a table as following:

ALTER TABLE guru99 RENAME TO guru100;

To verify that the table's name is changed, you can use the command ".tables" to show the list of tables and the table name should be changed now as following:

As you can see the table name "guru99" is changed to "guru100" after the "alter table" command.

SQLite add columns- Using ALTER TABLE Command

You can also use the "ALTER TABLE" command to add columns:

ALTER TABLE guru100 ADD COLUMN Age INT;

This will alter the table "guru100" and add a new column Age to it.

If you didn't see any output, this means that the statement was successful, and the column was added. No output means that the cursor will be positioned after "sqlite>" with no text after it like this

However, to verify that the column was added, we can use the command ".schema guru100" this will give you the table definition, and you should see the new column we have just added:

SQLite Insert value into a table

To insert values into a table, we use the "INSERT INTO" statement as follow:

INSERT INTO Tablename(colname1, colname2, ….) VALUES(valu1, value2, ….);

You can omit the columns names after the table name and write it as following:

INSERT INTO Tablename VALUES(value1, value2, ….);

In such case, where you are omitting the columns names from the tables, the number of inserted values must be the same exact number of the table's columns. Then each value will be inserted in the correspondence column. For example, for the following insert statement:

INSERT INTO guru100 VALUES(1, 'Mike', 25);

The result of this statement will be as following:

The value 1 will be inserted in the column "id".
The value 'Mike' will be inserted in the column "Name", and
The value 25 will be inserted in the column "Age".

INSERT ... DEFAULT VALUES statement

You can populate the table with the default values for the columns at once as following:

INSERT INTO Tablename DEFAULT VALUES;

If a column doesn't allow a null value nor a default value, you will get an error that "NOT NULL constraint failed" for that column. As following: