How to Rename SQLite Database Table Columns?

SQLite database table column renaming guide

Question: How to rename columns in a SQLite database table using common SQL statements?

Answer:

In earlier versions of SQLite, renaming table columns required a more complex process, including creating a temporary table, copying the data, deleting the original table, and renaming the temporary table. However, starting with SQLite version 3.25.0, released in September 2018, a more direct ALTER TABLE statement can be used:

<code class="language-sql">ALTER TABLE your_table
RENAME COLUMN "Really Bad : Column Name" TO BetterColumnName;</code>

For older versions of SQLite or for compatibility with other databases, the original "create new table and delete old table" method can still be used. This process includes the following steps:

1. Create temporary table: Define a new temporary table based on the structure of the old table, but with updated column names.

<code class="language-sql">CREATE TABLE tmp_table_name (
  col_a INT,
  col_b INT
);</code>

2. Copy data: Insert data from the original table into a temporary table using updated column names.

<code class="language-sql">INSERT INTO tmp_table_name(col_a, col_b)
SELECT col_a, colb
FROM orig_table_name;</code>

3. Delete old table: Delete original table.

<code class="language-sql">DROP TABLE orig_table_name;</code>

4. Rename temporary table: Change the name of the temporary table to the name of the original table.

<code class="language-sql">ALTER TABLE tmp_table_name RENAME TO orig_table_name;</code>

Remember to recreate any indexes, triggers, or other table-related objects affected by the rename. Additionally, consider wrapping this process in a transaction to ensure that all or none of the changes are applied successfully.

The above is the detailed content of How to Rename SQLite Database Table Columns?. For more information, please follow other related articles on the PHP Chinese website!