How to use SQLite ALTER TABLE for Other Actions In SQL

If we want to perform some other actions apart from the above, we need to use some fixed steps. For example, SQLite doesn’t support DROP COLUMN command. So we can use the procedure below to drop a column. Like in the above example, if we want to drop the column phone_number, the steps are:

1. Disable Foreign Key Constraints

We have to turn off the foreign key constraints temporarily because it gives us more flexibility in making changes.

PRAGMA  foreign_keys = OFF;

For Example: Let’s say there is another table having a foreign key referencing customers table, then dropping a column may result in violating referential integrity constraints. In order to improve flexibility and eliminate errors, we temporarily allow these types of violations.

2. Start a Transaction

To start a sequence of events, initiate a new transaction. We always have the option to return to the before state in case there is some error during a transaction.

BEGIN TRANSACTION;

3. Create a New Table Without the Column

Now, we will create a new table (customers_temp) which is same as the customers table, except that it does not have the phone_number field present in it.

CREATE TABLE IF NOT EXISTS customers_temp (
     customer_id INTEGER PRIMARY KEY,
     username TEXT
);

4. Copy Data From the Old Table to the New Table

Now, copy the data from the previous customers table to the new customers_temp table.

INSERT INTO customers_temp(customer_id, username)
SELECT customers_id, username
FROM customers;

5. Drop the Old Table

Since the data has been copied in the customers_temp table, we can now delete the previous customers table.

DROP TABLE customers;

6. Rename the New Table

Now that the change need to be applied to the customer_temp table and the old table has been removed, we will rename the table to customer.

ALTER TABLE customers_temp RENAME TO customers;

7. Commit the Transaction

We can now commit the transaction to ensure that all changes are made and committed.

COMMIT;

8. Enable Foreign Key Constraints

We can now turn foreign key constraints back on for ensuring data integrity.

PRAGMA foreign_keys = ON;

Now for every other action, we can use the same steps given above. We have to just make the changes directly to the new table and copy the relevant data from the old table to the new table.

SQLite is a serverless architecture that we use to develop embedded software for devices like televisions, cameras, and so on. It is written in C programming Language. It allows the programs to run without any configuration. In this article, we will learn everything about the ALTER TABLE command present in SQLite. In SQLite, the ALTER TABLE statement empowers you to make changes to the structure of an existing table without the need to recreate it entirely.