How to Add a New Column in Between Two Columns in SQLite

1. Add a New Column to the Existing SQLite Database
2. Create a New Table With Updated Columns
3. ALTER TABLE ADD COLUMN Approaches in Different Databases

In the SQLite database, we store data inside the tables. Each table contains rows and columns to maintain the data.

The column represents the attributes of the database, while the rows represent the record in each line. During the lifecycle of a database, if we want to insert or add a new column to the existing database, then we have multiple options to adopt and work on it.

One of the options is to insert a new column to the existing database or to create a new table with updated columns, copy the data inside the rows and delete the previous table.

Add a New Column to the Existing SQLite Database

To insert a new column into the database, we use the following syntax of the ALTER TABLE command to get the desired result. Here, we need to mention the table’s name and that we want to add a new column.

In the next line, we need to specify the column definition after the ADD COLUMN command.

ALTER TABLE name_of_the_table
ADD COLUMN new_column type_of_column;

The column definition is defined as follows.

name_of_the_column type_of_data constraint;

Looking at the above SQL statement, we added a single column to the database. However, we use the following SQL commands if we want to add multiple new columns to the SQL table.

ALTER TABLE name_of_the_table
ADD COLUMN new_column1_definition,
ADD COLUMN new_column2_definition;

Take an example of a customer’s table, which has 4 columns: customer_id as the primary key, c_first_name, c_last_name, and c_email of the customer. The description of the customer’s table is as follows.

CREATE TABLE customers (
    customers_id INT PRIMARY KEY,
    c_first_name VARCHAR(40) NOT NULL,
    c_last_name VARCHAR(40) NOT NULL,
    c_email VARCHAR(100) NOT NULL UNIQUE
);

A new requirement is to add a new column, phone_number, to the customers table. To accomplish this task, we need to use the ALTER TABLE command to provide the table’s name to add the new column.

In the next line, the ADD COLUMN command is needed to add a new column definition that contains the column name and its type.

ALTER TABLE customers
ADD COLUMN phone_number VARCHAR(35);

Similarly, if we want to insert multiple new columns into the SQL database, we need to explicitly use the ADD COLUMN command for each new column. Here, we have added three new columns to the customers database: c_mailing_address, c_dob, and c_social_media_account.

This approach adds the new columns at the end of a table, not in between the present columns.

Remember, there are different syntaxes to use the ALTER TABLE command in different databases, while the approach is the same.

ALTER TABLE customers
ADD COLUMN c_mailing_address VARCHAR(155),
ADD COLUMN c_dob DATE,
ADD COLUMN c_social_media_account VARCHAR(255);

Create a New Table With Updated Columns

The second option is to create a new table with the missing column and a temporary name. This approach places the column in the correct position and sequence.

Then, copy all the records from the previous table to the new table and delete the previous table. Later, rename the new table to the deleted table name.

Create a Table With a Temporary Name

The initial step is to create a new table with a temporary name with the same column names as the previous table columns. The same column names are necessary as we must copy all the records from the old table to the new one.

CREATE TABLE {NewTableTemporaryName}
(n_name TEXT, COLNew {type} DEFAULT {defaultValue}, quantity INTEGER, n_rate REAL);

Copy the Data From the Old Table

In the second step, we need to copy the records from the old table to the newly created table with the same columns. The INSERT INTO command helps us to insert all the data the SELECT clause provides.

INSERT INTO {NewTableTemporaryName}
(n_name, n_quantity, n_rate) SELECT n_name, n_quantity, n_rate FROM Old_Table;

Delete or Drop the Old Table

In the next step, once we copy all the records (rows) from the old table to the new one, we need to delete or drop the old one. The reason is we no longer need the old table records.

DROP TABLE Old_Table;

Rename the New Table With the Old Table Name

The last step is to rename the new table with the old table name. This is a much better approach as it allows us to rename everything according to the requirement.

After the last step, we have a table with a required name, updated columns, and all the records inside it.

ALTER TABLE {NewTabletemporaryName} RENAME TO Old_Table;

ALTER TABLE ADD COLUMN Approaches in Different Databases

We have already seen how to use the ADD COLUMN command to insert columns into the existing tables. This section details how to use the ALTER TABLE ADD COLUMN syntax in different databases.

These databases include PostgreSQL, MYSQL, Oracle, SQL Server, SQLite, and BD2.

PostgreSQL

ALTER TABLE name_of_the_table
ADD COLUMN column_definition;

MySQL

ALTER TABLE name_of_the_table
ADD [COLUMN] column_definition;

Oracle

ALTER TABLE name_of_the_table
ADD column_definition;

SQL Server

ALTER TABLE name_of_the_table
ADD column_definition;

SQLite

ALTER TABLE name_of_the_table
ADD COLUMN column_definition;

DB2

ALTER TABLE name_of_the_table
ADD column_definition;