SQLite add Primary Key

Question

I created a table in Sqlite by using the CREATE TABLE AS syntax to create a table based on a SELECT statement. Now this table has no primary key but I would like to add one.

Executing ALTER TABLE table_name ADD PRIMARY KEY(col1, col2,...) gives a syntax error "near PRIMARY"

Is there a way to add a primary key either during table creation or afterwards in Sqlite?

By "during creation" I mean during creation with CREATE TABLE AS.

Answer 1

You can't modify SQLite tables in any significant way after they have been created. The accepted suggested solution is to create a new table with the correct requirements and copy your data into it, then drop the old table.

here is the official documentation about this: http://sqlite.org/faq.html#q11

Answer 2

As long as you are using CREATE TABLE, if you are creating the primary key on a single field, you can use:

CREATE TABLE mytable ( field1 TEXT, field2 INTEGER PRIMARY KEY, field3 BLOB, ); 

With CREATE TABLE, you can also always use the following approach to create a primary key on one or multiple fields:

CREATE TABLE mytable ( field1 TEXT, field2 INTEGER, field3 BLOB, PRIMARY KEY (field2, field1) ); 

Reference: http://www.sqlite.org/lang_createtable.html

This answer does not address table alteration.