Definition of SQLite Alter Table
SQLite provides alter table functionality to the user, in which we can change the structure of existing tables. For example, we can add columns into the specified table or delete columns from the table, create or delete indexes, we can change the data type of the existing column, rename column name. If we use another connection for the alter command that means if metadata lock is active then the alter statement will wait until the lock is released. When we need to add a unique index on a column that specified duplicate values then alter command shows an error message and the statement execution will be stopped. The alter command is also used to rename the table name.
Syntax:
alter table specified existing table name rename to new specified table name;
Explanation:
In syntax we use alter table command to rename the table name, here specified existing table name means an existing table that already present in the database, new specified able name means new table name that we need to rename from old to new and rename and to is the keyword that is used in the above syntax.
How to alter table in SQLite?
Now let’s see how they to alter table command works in SQLite as follows.
Alter table rename
The RENAME TO is used to change the name of the existing table-name to the new-table-name. The alter table command can’t be utilized to move a table between joined information bases, just to rename a table inside a similar data set. In the event that the table being renamed has triggers or lists, these stay joined to the table after it has been renamed.
Alter table rename column
The RENAME COLUMN TO syntax changes the column name of the existing table-name into a new-column name. The new column name is changed both inside the table definition itself and furthermore inside all records, triggers, and perspectives that reference the column name. In the event that the section name change would bring about a semantic uncertainty in a trigger or view, at that point, the RENAME COLUMN fizzles with a mistake and no progressions are applied.
Alter table add column
The ADD COLUMN syntax structure is utilized to add another column to a current table. The new column is constantly affixed to the furthest limit of the rundown of existing columns. The specified column definition rule characterizes the qualities of the newly added column. The newly added column may take any of the structures allowable in a CREATE TABLE explanation, with the accompanying limitations:
The specified column name might not have a PRIMARY KEY or UNIQUE constraint from the specified table.
The specified column name might not have a default estimation of CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, or articulation in enclosures.
Assuming a NOT NULL requirement is indicated, the specified column should have default esteem other than NULL.
In the event that foreign keys have limitations and are empowered and a specified column with a REFERENCES provision is added, the column should have a default estimation of NULL.
The specified column name may not be GENERATED ALWAYS … Put away, however, VIRTUAL columns are permitted.
Examples
Now let’s see the different examples of alter table command as follows.
First, we need to create a new table by using the following statement as follows.
create table emp (emp_id integer primary key, emp_name text not null, emp_dept text not null, emp_salary text not null);
Explanation
In the above example, we use create table statement to create a new table name as emp with different attributes such as emp_id with integer data type and primary key constraint, emp_name with text data type and not a null constraint, emp_dept with text data type and not a null constraint and emp_salary with text data type and not a null constraint as shown in the above statement. The end result of the above statement is shown in the below screenshot.
.table
Now insert some duplicate records into the emp table as follows.
Insert into emp (emp_id, emp_name, emp_dept, emp_salary) values(6, “Johan”, “COMP”,”10000”), (7, “Jay”, “IT”,”40000”), (8, “Sameer”, “IT”,”15000”), (9, “Janny”, “MECH”,”5000”);
select * from emp;
Explanation
The end output of the above statement is shown in the below screenshot as follows.
4.5 (8,900 ratings)
View Course
Now perform the alter table command as follows.
Suppose we need to rename the existing table then we can use the following statement as follows.
alter table emp rename to employee;
Explanation
In the above example, we use alter table command with rename parameter; here we need to change the existing table name that emp to an employee as shown in the above statement. The end result of the above statement is shown below the screenshot.
.table
Now we can verify the table name by using the following statement as follows.
select emp_id, emp_name from employee;
Explanation
The end output of the above statement is shown in the below screenshot as follows.
By using an alter table we can add a new column to the existing table.
alter table employee add column city text;
Explanation
In the above example, we use alter table command with add column parameter as shown in the above example, here we need to add a new column that is city into the employee table.
For confirmation insert a single record into the employee table as follows.
Insert into employee (emp_id, emp_name, emp_dept, emp_salary, city) values(10, “Pooja”, “COMP”,”20000”, ”Mumbai”);
select * from employee;
Explanation
By using the above statement we insert records into the employee. Here we add the value for the newly added column as shown in the above statement. The end output of the above statement is shown in the below screenshot as follows.
By using alter table command can rename column name
alter table employee rename column city to emp_address;
Explanation
In the above statement, we use the alter table command with rename column parameter, here we change the address into the emp_address. The end output of the above statement is shown in the below screenshot as follows.
Similarly, we perform the other operation by using the alter table command.
Conclusion
We hope from this article you have understood about the SQLite alter table. From the above article, we have learned the basic syntax of alter table and we also see different examples of alter table. From this article, we learned how and when we use the SQLite alter table.
Recommended Articles
This is a guide to SQLite Alter Table. Here we discuss the Introduction, syntax, How to Alter Table in SQLite, and examples. You may also have a look at the following articles to learn more –
