Adding columns in SQLite Tables

SQLite Add Column

In SQLite, you can add a new column to an existing table using the ALTER TABLE statement. Adding a column allows you to expand your table's structure without recreating it or losing existing data. However, SQLite has some limitations:

• You can only add one column at a time.
• The new column must have a default value or allow NULL values.

Syntax

ALTER TABLE table_name ADD COLUMN column_name column_type [constraints];

Explanation:

• table_name: The name of the table to which you want to add the column.
• column_name: The name of the new column.
• column_type: The data type of the new column (e.g., TEXT, INTEGER, REAL).
• constraints: Optional constraints like DEFAULT, NOT NULL, etc.

Example:

Scenario: Add a new column to an existing employees table.

Before Adding the Column:

The employees table has the following structure:

We want to add a column named ‘salary’ to store employees' salaries.

Step 1: Check the current structure of the "employees" table

-- Use the PRAGMA table_info command to see the table's schema 
PRAGMA table_info(employees);  

Output:

-- cid | name       | type        | notnull | dflt_value | pk  
-- ----|------------|-------------|---------|------------|----  
-- 0   | id         | INTEGER     | 1       | NULL       | 1  
-- 1   | name       | TEXT        | 1       | NULL       | 0  
-- 2   | department | TEXT        | 0       | NULL       | 0  

Step 2: Add a new column "salary" of type INTEGER to the "employees" table

ALTER TABLE employees ADD COLUMN salary INTEGER;  

Step 3: Verify the updated structure of the "employees" table

PRAGMA table_info(employees);  

Output:

-- cid | name       | type        | notnull | dflt_value | pk  
-- ----|------------|-------------|---------|------------|----  
-- 0   | id         | INTEGER     | 1       | NULL       | 1  
-- 1   | name       | TEXT        | 1       | NULL       | 0  
-- 2   | department | TEXT        | 0       | NULL       | 0  
-- 3   | salary     | INTEGER     | 0       | NULL       | 0  

Step 4: Add sample data for the newly added column "salary"

-- Update the "salary" column for existing rows using the UPDATE statement  
UPDATE employees SET salary = 50000 WHERE id = 1;  
UPDATE employees SET salary = 55000 WHERE id = 2;  

Step 5: Query the "employees" table to view the updated data

SELECT * FROM employees;  

Output:

-- id  | name          | department   | salary  
-- ----|---------------|--------------|-------  
-- 1   | Alice Johnson | Marketing    | 50000  
-- 2   | Sara Smith     | Sales        | 55000

Explanation:

1. Checking the Table Structure:

• The PRAGMA table_info command is used to view the schema of the employees table before and after adding the column. This helps confirm changes to the table structure.

2. Adding a Column:

• The ALTER TABLE statement with the ADD COLUMN clause is used to add the new column (salary).
• SQLite allows adding only one column at a time, and it must either accept NULL values or have a default value.

3. Verifying the Updated Schema:

• After adding the column, the updated schema is displayed using PRAGMA table_info.

4. Updating Existing Data:

• Since the new column (salary) is initially empty (NULL), the UPDATE statement is used to populate it with relevant data for existing rows.

5. Querying the Table:

• A SELECT query is run to view the updated data in the employees table, confirming the addition and population of the salary column.

Additional Information

• You cannot remove or rename columns using the ALTER TABLE statement in SQLite. If such operations are required, you must create a new table with the desired structure, copy the data, and replace the old table.
• Adding constraints to a new column (e.g., NOT NULL) can only be done if the column allows a default value or is initially empty.

Practical Guides to SQLite Snippets and Examples.