SQLite Create, Alter, Drop Table with Examples

In this article, we will see how to create tables, modify tables and dropping tables in SQLite3 with examples.

In this tutorial, you will learn-

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

SQLite Create table

Syntax

Below is the syntax of CREATE TABLE statement.

CREATE TABLE table_name(
column1 datatype,
column1 datatype
);

To create a table, you should use the "CREATE TABLE" Query as follows:

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 follows:

DROP TABLE guru99;

Alter table

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

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 follows:

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 follows:

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: