SQLite Create, Alter, Drop Table with Examples

The sections below show how to create tables, modify their structure, and drop tables in SQLite3 with examples, covering the CREATE TABLE, ALTER TABLE, and DROP TABLE commands.

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 tutorial.

SQLite CREATE TABLE IF NOT EXISTS

When a script runs more than once, a plain CREATE TABLE statement fails if the table already exists and stops with an error. SQLite solves this with the optional IF NOT EXISTS clause, which creates the table only when it is missing and does nothing when it is already there.

CREATE TABLE IF NOT EXISTS guru99 (
  Id Int,
  Name Varchar
);

The statement above never raises the “table already exists” error, which makes it safe to keep at the top of a setup or migration script.

The same idea works in reverse when you remove a table. Adding IF EXISTS to DROP TABLE avoids an error when the table is already gone:

DROP TABLE IF EXISTS guru99;

Both clauses are common in automated scripts, because they let the same file run repeatedly without manual clean-up between runs.

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 ALTER TABLE: Rename and Drop a Column

Beyond adding a column, modern versions of SQLite can also rename a single column and remove one. Renaming a column has been available since SQLite 3.25, and it updates the name inside the table definition as well as in every index, trigger, and view that references the column.

ALTER TABLE guru100 RENAME COLUMN Name TO FullName;

To remove a column, SQLite 3.35 and later support a direct DROP COLUMN clause:

ALTER TABLE guru100 DROP COLUMN Age;

A column cannot be dropped when it is part of a primary key, a unique constraint, or an index. On older SQLite versions that lack DROP COLUMN, the classic workaround is to create a new table with the columns you want, copy the rows across, drop the old table, and finally rename the new one.

How to Copy a Table in SQLite Using CREATE TABLE AS SELECT

Sometimes you need a copy of a table, either as a backup before a risky change or as the starting point for a new table. SQLite builds this in one step with CREATE TABLE AS SELECT, which creates a new table whose columns come from a query and fills it with the rows that query returns.

CREATE TABLE guru100_backup AS SELECT * FROM guru100;

The new table “guru100_backup” receives the same columns and every row from “guru100”. To copy only some rows, add a WHERE clause to the SELECT:

CREATE TABLE known_ages AS SELECT * FROM guru100 WHERE Age IS NOT NULL;

One point to remember is that CREATE TABLE AS SELECT copies the column names, types, and data, but it does not copy primary keys, other constraints, or indexes. You need to add those to the new table yourself if the copy must enforce the same rules as the original.

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:

FAQs