SQLite Alter 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.

ALTER TABLE Command in SQLite

This command is used to change the structure of a table that is already present in the database schema. SQLite supports only some of the functionalities of the ALTER TABLE command which is not the case with other SQL databases. So, let’s see how to perform changes in the database table using the ALTER TABLE command.

Using SQLite ALTER TABLE to Rename a Table

To rename a table, you can use the following syntax

ALTER TABLE old_table RENAME TO new_table;

For Example:

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    username TEXT
);

ALTER TABLE users RENAME TO customers;

Output:

users table to customers table

Here we created a table named users in our database which is then renamed to customers. Random data has been inserted into the table to make things more clear.

Using SQLite ALTER TABLE to Add a New Column to a Table

We can add a new column to an existing table by using the following syntax:

ALTER TABLE  table_name  ADD COLUMN  new_column  column_definition;

Continuing the above example for the table customers, we want to add a new phone_number field. So, we use:

ALTER TABLE  customers  ADD COLUMN  phone_number  TEXT;

Output:

customers table

Now the customers table has a new field, phone_number which is of type text.

Hence, the all the fields of the customers table are user_id, username, and phone_number.

Using SQLite ALTER TABLE to Rename a Column

To rename an existing column, we can use the following syntax:

ALTER TABLE  table_name  RENAME COLUMN  old_column  TO  new_column;

Now, in the above example, we want to change the column named user_id to customer_id. So we use:

ALTER TABLE customers RENAME COLUMN user_id TO customer_id;

Output:

customers table

Using SQLite ALTER TABLE for Other Actions

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

customers_temp table

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;

customers_temp table

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;

customers table

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.

Conclusion

In conclusion, the ALTER TABLE statement in SQLite is a key asset for dynamic database management. Its ability to modify table structures with simplicity and precision ensures adaptability to evolving data needs. Whether fine-tuning existing columns or introducing new ones, this feature empowers developers to efficiently navigate changes in their database schema, promoting a fluid and responsive data architecture.