SQLite: ALTER TABLE Statement
This SQLite tutorial explains how to use the SQLite ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table (with syntax and examples).
Description
The SQLite ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The SQLite ALTER TABLE statement is also used to rename a table.
Add column in table
Syntax
The syntax to ADD A COLUMN in a table in SQLite (using the ALTER TABLE statement) is:
ALTER TABLE table_name ADD new_column_name column_definition;
- table_name
- The name of the table to modify.
- new_column_name
- The name of the new column to add to the table.
- column_definition
- The datatype and definition of the column (NULL or NOT NULL, etc).
Example
Let's look at an example that shows how to add a column in a SQLite table using the ALTER TABLE statement.
For example:
ALTER TABLE employees ADD status VARCHAR;
This SQLite ALTER TABLE example will add a column called status to the employees table. It will be created as a column that allows NULL values.
Modify column in table
You can not use the ALTER TABLE statement to modify a column in SQLite. Instead you will need to rename the table, create a new table, and copy the data into the new table.
Syntax
The syntax to MODIFY A COLUMN in a table in SQLite is:
PRAGMA foreign_keys=off; BEGIN TRANSACTION; ALTER TABLE table1 RENAME TO _table1_old; CREATE TABLE table1 ( ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ], ... ); INSERT INTO table1 (column1, column2, ... column_n) SELECT column1, column2, ... column_n FROM _table1_old; COMMIT; PRAGMA foreign_keys=on;
Example
Let's look at an example that shows how to modify a column in a SQLite table.
For example, if we had an employees table that had a column called last_name that was defined as a CHAR datatype:
CREATE TABLE employees ( employee_id INTEGER PRIMARY KEY AUTOINCREMENT, last_name CHAR NOT NULL, first_name VARCHAR, hire_date DATE );
And we wanted to change the datatype of the last_name field to VARCHAR, we could do the following:
PRAGMA foreign_keys=off; BEGIN TRANSACTION; ALTER TABLE employees RENAME TO _employees_old; CREATE TABLE employees ( employee_id INTEGER PRIMARY KEY AUTOINCREMENT, last_name VARCHAR NOT NULL, first_name VARCHAR, hire_date DATE ); INSERT INTO employees (employee_id, last_name, first_name, hire_date) SELECT employee_id, last_name, first_name, hire_date FROM _employees_old; COMMIT; PRAGMA foreign_keys=on;
This example will rename our existing employees table to _employees_old. Then it will create the new employees table with the last_name field defined as a VARCHAR datatype. Then it will insert all of the data from the _employees_old table into the employees table.
Drop column in table
You can not use the ALTER TABLE statement to drop a column in a table. Instead you will need to rename the table, create a new table, and copy the data into the new table.
Syntax
The syntax to DROP A COLUMN in a table in SQLite is:
PRAGMA foreign_keys=off; BEGIN TRANSACTION; ALTER TABLE table1 RENAME TO _table1_old; CREATE TABLE table1 ( ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ], ... ); INSERT INTO table1 (column1, column2, ... column_n) SELECT column1, column2, ... column_n FROM _table1_old; COMMIT; PRAGMA foreign_keys=on;
Example
Let's look at an example that shows how to drop a column in a SQLite table.
For example, if we had an employees table that was defined as follows:
CREATE TABLE employees ( employee_id INTEGER PRIMARY KEY AUTOINCREMENT, last_name VARCHAR NOT NULL, first_name VARCHAR, hire_date DATE );
And we wanted to drop the column called hire_date, we could do the following:
PRAGMA foreign_keys=off; BEGIN TRANSACTION; ALTER TABLE employees RENAME TO _employees_old; CREATE TABLE employees ( employee_id INTEGER PRIMARY KEY AUTOINCREMENT, last_name VARCHAR NOT NULL, first_name VARCHAR ); INSERT INTO employees (employee_id, last_name, first_name) SELECT employee_id, last_name, first_name FROM _employees_old; COMMIT; PRAGMA foreign_keys=on;
This example will rename our existing employees table to _employees_old. Then it will create the new employees table with the hire_date field removed. Then it will insert all of the data (excluding the hire_date field) from the _employees_old table into the employees table.
Rename column in table
You can not use the ALTER TABLE statement to rename a column in SQLite. Instead you will need to rename the table, create a new table, and copy the data into the new table.
Syntax
The syntax to RENAME A COLUMN in a table in SQLite is:
PRAGMA foreign_keys=off; BEGIN TRANSACTION; ALTER TABLE table1 RENAME TO _table1_old; CREATE TABLE table1 ( ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ], ... ); INSERT INTO table1 (column1, column2, ... column_n) SELECT column1, column2, ... column_n FROM _table1_old; COMMIT; PRAGMA foreign_keys=on;
Example
Let's look at an example that shows how to rename a column in a SQLite table.
For example, if we had an employees table that was defined as follows:
CREATE TABLE employees ( employee_id INTEGER PRIMARY KEY AUTOINCREMENT, last_name VARCHAR NOT NULL, first_name VARCHAR, hire_date DATE );
And we wanted to rename the hire_date field to start_date, we could do the following:
PRAGMA foreign_keys=off; BEGIN TRANSACTION; ALTER TABLE employees RENAME TO _employees_old; CREATE TABLE employees ( employee_id INTEGER PRIMARY KEY AUTOINCREMENT, last_name VARCHAR NOT NULL, first_name VARCHAR, start_date DATE ); INSERT INTO employees (employee_id, last_name, first_name, start_date) SELECT employee_id, last_name, first_name, hire_date FROM _employees_old; COMMIT; PRAGMA foreign_keys=on;
This example will rename our existing employees table to _employees_old. Then it will create the new employees table with the hire_date field called start_date. Then it will insert all of the data from the _employees_old table into the employees table.
Rename table
Syntax
To rename a table, the SQLite ALTER TABLE syntax is:
ALTER TABLE table_name RENAME TO new_table_name;
- table_name
- The table to rename.
- new_table_name
- The new table name.
Example
Let's look at an example that shows how to rename a table in SQLite using the ALTER TABLE statement.
For example:
ALTER TABLE employees RENAME TO staff;
This ALTER TABLE example will rename the employees table to staff.
Advertisements