Here we will learn how to use sqlite alter table statement to modify or add the column to a table, sqlite rename columns in the table, sqlite drop columns in a table with examples.
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.
Following is the syntax of adding a column to an existing table using the alter table statement in sqlite.
ALTER TABLE table_name ADD new_column_name column_definition;
In the above syntax, we have different properties to add a new column to the 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 data type and definition of the column (NULL or NOT NULL, etc).
Now, let’s look at the example of adding the column to a 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.
To rename a table in SQLite, the following syntax is used.
ALTER TABLE table_name RENAME TO new_table_name;
In the above syntax, we are renaming the table by using RENAME property. 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 the example of rename table products to product_master in SQLite.
ALTER TABLE products RENAME TO product_master;
In the above example, we are renaming table name from products to product_master. This way we can rename the table name in SQLite.
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 the 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 the 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.
In SQLite, we cannot directly use the 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 the Quantity column from the 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 didn't 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 a column in SQLite.
In SQLite, we can not directly use ALTER TABLE statement to rename column of the table for that first we have to rename table then create a new table and copy the data of the old table to the new table.
Following is the syntax of the 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 a 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 the 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.