Add a Column to an Existing Table in SQLite

by

In SQLite, you can use the ALTER TABLE statement to add a column to an existing table.

This is actually one of the few things you can do with the ALTER TABLE statement in SQLite. The only things you can do with the ALTER TABLE statement in SQLite is rename a table, rename a column, and add a new column to an existing table.

Example 1

Imagine we have the following table:

CREATE TABLE Cats( 
    CatId INTEGER PRIMARY KEY, 
    CatName
);

And we now want to add a column called DOB. We could do that using the following code:

ALTER TABLE Cats ADD COLUMN DOB;

It’s as simple as that.

Restrictions

You can also provide other specifications to the definition, such as constraints, etc, but there are some restrictions.

In particular:

  • The new column cannot have a PRIMARY KEY or UNIQUE constraint.
  • The new column cannot have a default value of CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, or an expression in parentheses.
  • If a NOT NULL constraint is specified, then the column must have a default value other than NULL.
  • If foreign key constraints are enabled and a column with a REFERENCES clause is added, the column must have a default value of NULL.
  • If the new column is a generated column, it cannot be STORED column. It can be a VIRTUAL column though.

Example 2

Here’s another example, this time I add some more specifications to the definition of the new column:

CREATE TABLE Dogs( 
    DogId INTEGER PRIMARY KEY, 
    DogName
);

And we now want to add a column called DOB. We could do that using the following code:

ALTER TABLE Dogs 
ADD COLUMN Score NOT NULL DEFAULT 0;

Check the Alterations

There are several ways to check the structure of a table in SQLite.

One way is to use the PRAGMA table_info() statement.

PRAGMA table_info(Dogs);

Result:

cid  name     type     notnull     dflt_value  pk
---  -------  -------  ----------  ----------  --
0    DogId    INTEGER  0                       1 
1    DogName           0                       0 
2    Score             1           0           0 

We can see the new column, and we can see that it has a NOT NULL constraint, and that its default value is 0.