Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

How to Add and Drop a Column with SQLite ALTER TABLE Statement – Querychat

The ALTER TABLE in SQLite only allows us to either rename a table or add a column to the table. However, we cannot use that statement for deleting columns in SQLite. In this article, we will find out how to add a column in SQLite using the ALTER TABLE statement and also how to drop a column in SQLite.

SQLite ALTER TABLE ADD COLUMN

The ALTER TABLE allows us to add a new column to our table, and this new column will be added at the end of our column list. 

The syntax of this command is: 

ALTER TABLE our_table_name

COLUMN column_name column_type;

Where: 

  • our_table_name is the name of our existing table.
  • column_name is the name of the column we want to add.
  • column_type is the type of the new column.

Note: The newly-created column cannot have the PRIMARY KEY constraint as well as the UNIQUE. If we specify the NOT NULL, then we need to specify the not-null default value.

Example

Let’s start by creating a table called “Dogs”, where we will add a column later.

The script will be:  

CREATE TABLE IF NOT EXISTS Dogs (

idDog INT NOT NULL,

breed VARCHAR (20) NOT NULL,

evaluation_criteria VARCHAR (10) NOT NULL

);

Result:

Figure 1. The “Dogs” table

Then, add a new column called “Life_span” with the INTEGER type to our table with the use of this script: 

ALTER TABLE Dogs ADD COLUMN Life_span INTEGER;

Now we have 4 columns in our table, and the new one was added at the end of the list.

SELECT * FROM Dogs;

Result: 

Figure 2. The “Dogs” table with a new column

SQLite ALTER TABLE DROP COLUMN

There is no such command as  ALTER TABLE DROP COLUMN in SQLite. To drop a column in this database, we need to use another way. 

Literally, we need to make a copy of our table, but without adding the unneeded column. And then, we will copy the data from the original data avoiding the odd column.

Step 1.

Before we start, turn off the foreign key constraint check by using the following script:

PRAGMA foreign_keys=off;

Step 2.

Start a new transaction that will contain all our actions: 

BEGIN TRANSACTION;

Step 3. 

Now, we can drop a column by creating a new table without the unneeded column.

We will use a script:

CREATE TABLE IF NOT EXISTS DogsCopy (

 idDogsCopy INTEGER PRIMARY KEY,

 breed TEXT NOT NULL,

 evaluation_criteria TEXT NOT NULL

);

Step 4.

Copy the data from our original “Dogs” table to the new ‘DogsCopy” table, ignoring the unneeded column.

INSERT INTO DogsCopy(idDogsCopy, breed, evaluation_criteria)

SELECT idDog, breed, evaluation_criteria

FROM Dogs;

Step 5.

Drop the old “Dogs” table that contains the “Life_span” column.

DROP TABLE Dogs;

Step 6.

Now the “DogsCopy” becomes our original table so we can rename it to “Dogs”.

ALTER TABLE DogsCopy RENAME TO Dogs;

Step 7.

Commit the transaction.

COMMIT;

Step 8. 

Finally, turn on the foreign key constraint check, and it’s important not to forget this step.

PRAGMA foreign_keys=on;

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc