Updated April 3, 2023
Introduction to SQLite add column
SQLite provides an add column functionality to the user, in which we can change the structure of existing tables. For example, we can add columns into the specified table then we can easily add new columns with the data type. 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. By using the alter command we can easily add a new column to the existing table.
Syntax
alter table specified table name add column specified column name data type;Explanation
In the above syntax we use alter table command we add a new column into the existing table, here the specified table name means the actual table name that we need to change, add and the column is a keyword and the specified column name means the new column name that we need to add into the existing table.
How to add a column in SQLite?
Now let’s see how they add column works into the SQLite as follows.
The ADD COLUMN syntax is utilized to add another new column into the current table. The new column is constantly added to the furthest limit of the rundown of existing columns. The new column rule characterizes the qualities of the new segment. The new column may take any of the structures passable in a CREATE TABLE statement, with the accompanying limitations:
- The new column might not have a PRIMARY KEY or UNIQUE requirement.
- The new column might not have a default estimation of CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, or an expression in brackets.
- On the off chance that a NOT NULL limitation is determined, the column should have default value other than NULL.
- In the event that foreign key imperatives are empowered and a column with a REFERENCES clause is added, the column should have a default estimation of NULL.
- The column may not be GENERATED ALWAYS … Put away, however, VIRTUAL columns are permitted.
Note likewise that while adding a CHECK constraint, the CHECK constraint isn’t tried against previous rows of the table. This can bring about a table that contains information that is infringing upon the CHECK constraint. Future adaptations of SQLite may change to approve CHECK constraints as they are added.
We can use SQL text with the ALTER TABLE command by adjusting the mapping in the sqlite_schema table. No progressions are made to table substance for renames or column expansion. Along these lines, the execution season of such ALTER TABLE commands is free of the measure of information in the table. They run as fast on a table with 10 million columns as on a table with 1 row.
Sometimes alter table commands create problems in the SQLite as follows.
Most SQL database engines store the outline previously parsed into different system tables. On that database engine, ALTER TABLE only needs to make alterations to the relating framework tables.
SQLite is distinctive in that it stores the outline in the sqlite_schema table as the first content of the CREATE statement that characterizes the mapping. Subsequently, ALTER TABLE requirements to overhaul the content of the CREATE statement. Doing so can be precarious for certain “inventive” blueprint plans.
Examples
Now let’s see the different examples of add columns in SQLite 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 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 null constraint, emp_dept with text data type and not null constraint and emp_salary with text data type and not null constraint as shown in the above statement. The end result of the above statement is shown below screenshot.
.table
Now insert some records into the emp table by using insert into the statement as follows.
Insert into emp (emp_id, emp_name, emp_dept, emp_salary) values (1, "Johan", "COMP","10200"), (2, "Jay", "IT","20000"), (3, "Sameer", "COMP","15000"), (4, "Janny", "MECH","5000"), (5, "Pooja", "MECH","27000");
select * from emp;Explanation
In the above statement, we use to insert into the statement to insert new records into the emp table. The end result of the above statement is shown in the screenshot below.
Now we can perform the add column by using the following statement as follows.
Suppose a user needs to add a dept_id column into the emp table at that time user can use the following statement as follows.
alter table emp add column dept_id;For confirmation add a single row as follows.
insert into emp (emp_id, emp_name, emp_dept, emp_salary, dept_id) values (6, "Ajay", "COMP","10200","001");
select * from emp;Explanation
In the above example, we first add the dept_id column by using the alter table command, after that, we perform an insert operation for confirmation as shown in the above statement. The end result of the above statement is shown in the screenshot below.
Now see another example as follows.
alter table emp add column emp_score not null default 1;Explanation
In the above example we use the alter table command here we added emp_score with not null constraint and also we also set the default constraint as shown in the above statement.
Now we can check the structure of the table by using the following statement as follows.
pragma table_info(emp);The end result of the above statement is shown in the screenshot below.
Conclusion
We hope from this article you have understood about the SQLite add column. From the above article, we have learned the basic syntax of SQLite add column and we also see different examples of SQLite add column. We also learned the rules of SQLite add columns. From this article, we learned how and when we use the SQLite add column.
Recommended Articles
We hope that this EDUCBA information on “SQLite add column” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
