Add primary key to an existing table-CodePudding

How to add a primary key to an existing table in SQLite? I know that I have to generate a new table and copy everything from the old table. However, it keeps giving me an error saying datatype mismatch due to one table having a primary key and the other one not. I did run the same commands without including primary key and it works.

CREATE TABLE temp_table 
(
    id INTEGER PRIMARY KEY, 
    age INTEGER, 
    address TEXT
)

INSERT INTO temp_table 
    SELECT * 
    FROM original_table

Since I am importing the data from a CSV file I do not know how to add the PRIMARY KEY in the first place. In case anyone knows a solution for that it would also work.

CodePudding user response：

Assuming that the original table has 2 columns: age and address, you can either list the column names of the new table without the primary key:

INSERT INTO temp_table(age, address) 
SELECT age, address FROM original_table

or, include the primary key and pass null for its value:

INSERT INTO temp_table(id, age, address) 
SELECT null, age, address FROM original_table

or:

INSERT INTO temp_table
SELECT null, age, address FROM original_table

In all cases the id will be filled by SQLite, because it is defined as INTEGER PRIMARY KEY and it will be automatically autoincremented starting from 1.

If there is another column in the original table with unique integer values, you can pass that column to fill the new id:

INSERT INTO temp_table(id, age, address) 
SELECT col, age, address FROM original_table

Change col to the actual name of the column.