Altering the database is applied to make changes to the tables and their data. The ALTER TABLE instruction of the SQLite database has been specially designed to perform some of the very basic yet useful operations with a single stroke. Let’s say, you want to change the name of a table and a column with the ALTER TABLE instruction. Or, you need to add or remove the columns from the table. For all these options, you need to use the different clauses within the ALTER TABLE query.
This step is very necessary when you have been working on your Linux system often to make it up to date with new versions, and make it bug-free.
So first, you need to open our Linux terminal with the help of the Ctrl+Alt+T shortcut. Then, try out the update command to make your system up to date. Use the apt package in this command with sudo rights. It may ask for your password to continue this process. Provide your password and your system is ready to go.
After a successful update, our system may require the upgrade of some of the records. For this, try out the upgrade instruction along with the apt package in the shell preceded by the sudo rights as well.
While processing, it asks a question to confirm the continuation of an upgrade process. Tap the “y” button to continue this process.
The process will be completed in a while.
Right-now, we have no tables in the database as per the “.tables” instruction. So, we need to create one. Therefore, we have been trying the CREATE TABLE instruction to generate a new table Test within the database that will contain three columns (i.e ID, Name, and AGE.) The ID and AGE column will be of integer type and the column Name will be of text type.
Now, we have to use the INSERT INTO instruction to add values within the ID, Name, and AGE column of a Test table followed by the keyword “VALUES” and records. We have added a total of 5 records in this table Test as shown. After inserting all the records, we have been displaying the data of a table Test using the SELECT instruction following the “*” character. A total of 5 records have been displayed.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | .tables CREATE TABLE Test(ID INT NOT NULL, Name TEXT, AGE INT); INSERT INTO Test(ID, Name, AGE) VALUES (1, "John", 25); INSERT INTO Test(ID, Name, AGE) VALUES (2, "Ana", 20); INSERT INTO Test(ID, Name, AGE) VALUES (3, "Peter", 30); INSERT INTO Test(ID, Name, AGE) VALUES (4, "William", 27); INSERT INTO Test(ID, Name, AGE) VALUES (5, "Robert", 29); SELECT * FROM Test; |
Rename the Table Title:
Using the ALTER TABLE instruction, we can also update the name of an already existing table. This command must be used with the RENAME TO clause to change a specific table name. So, we will be covering an example of updating a table name for a table Test in our SQLite table. We will be updating “Test” to “Teacher”.
We have been using the ALTER TABLE instruction along with the name of a table “Test” followed by the RENAME TO clause that has been following the new name of a table (i.e. Teacher.) The name changed successfully. After fetching the list of tables using the “.tables” instruction, we have the table “Teacher” instead of “Test” as per the update.
1 2 3 | ALTER TABLE Test RENAME TO Teacher; .tables |
Now, we will take a look at how a new name has been added to the existing “Test” table? For this, you need to try out the SELECT instruction with the old table name first (i.e. Test.) The execution of this instruction leads us to an error “no such table: Test”. This means, the table name has been modified and we have to use a new name instead of the old name to perform any query.
Therefore, we have tried the SELECT instruction again to display the values of a table with the use of a modified table name (i.e. Teacher.) The execution of this instruction has been displaying the set of 5 records for the updated Teacher table as presented below.
1 2 3 | SELECT * FROM Test; SELECT * FROM Teacher; |
Add New Column in Table:
The ALTER TABLE instruction can also be utilized to add a new column within the existing table without any issue. Suppose you have a Teacher table within your database. This table “Teacher” contains a total of 3 columns with 5 record (i.e. ID, Name, Age.) Let’s add a new column within the Teacher table with the use of ALTER TABLE instruction.
For this, you need to use the ADD COLUMN clause in the ALTER TABLE query. You need to specify the table name after the ALTER TABLE clause followed by the ADD COLUMN clause and the name of a new column to be added to the table i.e. Salary. The Salary column has been successfully added to the Teacher table as the SELECT instruction displaying the output with an extra “|” character. Right-now the column “Salary” is empty as shown.
1 2 3 | ALTER TABLE Teacher ADD COLUMN Salary; SELECT * FROM Teacher; |
Let’s try out the ALTER TABLE instruction once again to add a new column to the existing table. So, we have been adding the column Qualification with its type “text” using the ADD COLUMN clause within the ALTER TABLE instruction as displayed. The column has been added successfully and we have fetched the records of a Teacher table as shown. Now, the table output shows two extra “|” character signs (i.e. 2 new columns added to the table.)
1 2 3 | ALTER TABLE Teacher ADD COLUMN Qualification TEXT SELECT * FROM Teacher; |
Conclusion:
This guide was written for the users of SQLite databases to help them understand the main concept of using the ALTER TABLE instruction. We have discussed the ADD COLUMN and RENAME TO clauses to enhance the level of understandability.
