SQLite Alter Table

Here we will learn how to use sqlite alter table statement to modify or add column to table , sqlite rename columns in table, sqlite drop columns in table with examples.

SQLite Alter Table

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.

SQLite Add Column to Table

Following is the syntax of adding column to existing table using alter table statement in sqlite.

ALTER TABLE table_name ADD new_column_name column_definition;

In above syntax we have different properties to add new column to table by using sqlite alter table statement those are

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

Now, let’s look at the example of adding column to table using SQLite alter table statement.

ALTER TABLE emp_master ADD dept_id INTEGER REFERENCES dept_master(dept_id);

This SQLite ALTER TABLE example will add a column called dept_id to emp_master table and this column created as a foreign key to dept_master.

SQLite Rename Table

To rename table in SQLite, following syntax is used

ALTER TABLE table_name RENAME TO new_table_name;

 In above syntax we are renaming table by using RENAME propoerty. Following are the few properties which we used.

table_name: The old table that you want to rename.

new_table_name: The new table name that you wish to give.

Now let’s look at example of rename table products to product_master in SQLite.

ALTER TABLE products RENAME TO product_master;

In above example we are renaming table name from products to product_master. This way we can rename table name in sqlite.

SQLite Modify Column in Table

In SQLite we cannot use ALTER TABLE statement to modify a column, instead we need to rename the table, create a new table, and copy the data into the new table.

Following is the syntax of modifying table column in sqlite.

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)

SELECT column1, column2

FROM _table1_old;

COMMIT;

PRAGMA foreign_keys=on;

 Now let look at the example of modifying column type. Assume that we have one table called products as follows.

CREATE TABLE products

(

Product_id INTEGER PRIMARY KEY,

Product_name VARCHAR NOT NULL,

Quantity INTEGER

)

 Now, if you want to change the data type of Product_name to TEXT then we have to do as following.

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE products RENAME TO _products_old;

CREATE TABLE products

(

Product_id INTEGER PRIMARY KEY,

Product_name TEXT NOT NULL,

Quantity INTEGER

);

INSERT INTO products(Product_id, Product_name, Quantity)

SELECT Product_id, Product_name, Quantity

FROM _products_old;

COMMIT;

PRAGMA foreign_keys=on;

In above example we renamed products table to _product_old then we created another table called products in which we declared Product_name as TEXT data type. After that we inserted all the data of _products_old to products.

This is the only way to modify column type in SQLite.

SQLite Drop Column in Table

In SQLite we cannot directly use ALTER TABLE statement to drop a column in a table. Instead we need to rename the table then create a new table, and copy the data into the new table same as modifying table column type.

Following is the syntax of dropping table column in sqlite.

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)

SELECT column1, column2

FROM _table1_old;

COMMIT;

PRAGMA foreign_keys=on;

 Now let’s look at example that shows dropping column in SQLite. Suppose we have table called Products as follows.

CREATE TABLE products

(

Product_id INTEGER PRIMARY KEY,

Product_name VARCHAR NOT NULL,

Quantity INTEGER

)

 Now if we want to drop Quantity column from products table then we have to do as following.

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE products RENAME TO _products_old;

CREATE TABLE products

(

Product_id INTEGER PRIMARY KEY,

Product_name VARCHAR NOT NULL,

);

INSERT INTO products(Product_id, Product_name)

SELECT Product_id, Product_name

FROM _products_old;

COMMIT;

PRAGMA foreign_keys=on;

 In this example we renamed products table to _products_old then we created another table called products in which we have not specify Quantity as column and we insert all the data of product_id and product_name from _products_old to product. This is the only way to dropping column in SQLite.

SQLite Rename Column in Table

In SQLite, we can not directly use ALTER TABLE statement to rename column of table for that first we have to rename table then create new table and copy the data of old table to new table.

Following is the syntax of renaming table column in sqlite.

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)

SELECT column1, column2

FROM _table1_old;

COMMIT;

PRAGMA foreign_keys=on;

Now, let’s look at the example that shows how to rename table column in SQLite. Assume that we have one table called products as follows.

CREATE TABLE products

(

Product_id INTEGER PRIMARY KEY,

Product_name VARCHAR NOT NULL,

Quantity INTEGER

)

 Now if we want to rename the column Quantity to Qty then we need to do as follows.

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE products RENAME TO _products_old;

CREATE TABLE products

(

Product_id INTEGER PRIMARY KEY,

Product_name VARCHAR NOT NULL,

Qty INTEGER

);

INSERT INTO products(Product_id, Product_name,Quantity)

SELECT Product_id, Product_name, Qty

FROM _products_old;

COMMIT;

PRAGMA foreign_keys=on;

In above example first we rename the products table to _products_old then we have created one another table called products in which we have declared column called Qty with INTEGER data type. After that we inserted all the data of _products_old to products. This is the only way to rename table column in SQLite.

This is how we can use SQLite ALTER TABLE statement to alter table name, column name, remove or add columns to tables based on our requirements.