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;
- 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.
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 );
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;
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.
Before we start, turn off the foreign key constraint check by using the following script:
Start a new transaction that will contain all our actions:
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 );
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;
Drop the old “Dogs” table that contains the “Life_span” column.
DROP TABLE Dogs;
Now the “DogsCopy” becomes our original table so we can rename it to “Dogs”.
ALTER TABLE DogsCopy RENAME TO Dogs;
Commit the transaction.
Finally, turn on the foreign key constraint check, and it’s important not to forget this step.
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.