sqlite3 add a primary key to new table created by query result

Question

I want to create a table using query result. But I want to also add a auto increment primary key field to it. Is it possible to achieve it using sql?

Example:
Select two field from table_a. But want the output schema as (id, field_a, field_b)

create table as foo

    select field_a, field_b

    from tablel_a

Currently using sqlite 3. But other database system solution is also fine.

Answer 1

This is not possible with a single statement; CREATE TABLE ... AS ... does not create constraints.

You have to use two statements:

CREATE TABLE foo ( ID INTEGER PRIMARY KEY, [...] );
INSERT INTO foo (...) SELECT ...;

sqlite3 add a primary key to new table created by query result

Sign up