CODEWITHSUNDEEP
sqlsqlite
xuanyue
xuanyue

Reputation: 1428

add a primary key to new table created by query result

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 SQLite?

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

create table foo as
    select field_a, field_b
    from tablel_a

Currently using SQLite 3, but solutions using other database systems are also fine.

Upvotes: 0

Views: 134

Answers (2)

CL.
CL.

Reputation: 180020

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 ...;

Upvotes: 1

Dror
Dror

Reputation: 5495

by default sqlite adds a rowid column in every table you create , so unless there's some specific need here, you can use this rowid column

check this out https://www.sqlite.org/lang_createtable.html#rowid

Upvotes: 1

Related Questions