How to Remove a Column from an SQLite Table?

Deleting Columns in SQLite Databases: A Practical Guide

Removing a column from an SQLite table using the standard ALTER TABLE statement isn't directly supported. The SQLite documentation explicitly states this limitation. Therefore, a workaround is necessary.

The recommended approach involves recreating the table. This process is detailed below:

1. Create a Temporary Table: Construct a temporary table with the desired schema, omitting the column you wish to remove.

2. Transfer Data: Copy data from the original table into the temporary table, excluding the unwanted column.

3. Remove the Original Table: Delete the original table.

4. Recreate the Table: Create a new table with the updated column structure (without the deleted column).

5. Restore Data: Insert the data from the temporary table into the newly created table.

6. Remove the Temporary Table: Finally, delete the temporary table.

Here's a SQL example illustrating this process:

<code class="language-sql">BEGIN TRANSACTION;
CREATE TEMPORARY TABLE temp_table (a, b);
INSERT INTO temp_table SELECT a, b FROM original_table;
DROP TABLE original_table;
CREATE TABLE original_table (a, b);
INSERT INTO original_table SELECT a, b FROM temp_table;
DROP TABLE temp_table;
COMMIT;</code>

Remember to replace original_table with your actual table name and a, b with the names of the columns you want to retain. This method ensures data integrity while effectively removing the specified column.

The above is the detailed content of How to Remove a Column from an SQLite Table?. For more information, please follow other related articles on the PHP Chinese website!