SQLite alter table syntax examples

SQLite FAQ: Can you show me how the SQLite ALTER TABLE syntax works?

SQLite Solution

At the time of this writing you can use the SQLite ALTER TABLE syntax for two purposes:

1. Add a column to the end of an existing SQLite database table
2. Change the name of a database table.

For other changes you'll have to follow some workaround procedures (discussed below).

How to add a column with ALTER TABLE

You can add a column to an existing SQLite table with the SQLite "ALTER TABLE ... ADD COLUMN" syntax, as shown in the following code. First, create a simple database table to work with:

CREATE TABLE test1(a,b);

As you can see, the SQLite CREATE TABLE syntax is pretty forgiving, and you don't even have to specify the data types of the table columns. This means that you can also add a new column without specifying its type, like this:

ALTER TABLE test1 ADD COLUMN foo;

Or you can specify a type, if you prefer:

ALTER TABLE test1 ADD COLUMN bar TEXT;

You can even go crazy, and specify a type and a default value with the SQLite ALTER TABLE syntax:

ALTER TABLE test1 ADD COLUMN baz TEXT NOT NULL DEFAULT 'baz';

How to change a database table name with ALTER TABLE

As mentioned, you can also use the SQLite ALTER TABLE syntax to change the name of an existing database table. Here's an example to demonstrate this alter table syntax:

sqlite> alter table order_items rename to line_items;

More complicated ALTER TABLE examples

As the SQLite documentation mentions, there are many things you can’t accomplish with the ALTER TABLE command at this time. The following text is copied from the SQLite FAQ:

"SQLite has limited ALTER TABLE support ... if you want to make more complex changes in the structure of a table, you will have to recreate the table ...

For example, suppose you have a table named 't1' with columns names 'a', 'b', and 'c' and that you want to delete column 'c' from this table. The following steps illustrate how this could be done:"

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

Summary: SQLite alter table examples

I hope these SQLite alter table examples are helpful. As usual, if you have any questions, just leave a note in the Comments section below.