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?

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

Answer 1

You can do it like this:

"CREATE TABLE mytable ("
"field1 text,"
"field2 text,"
"field3 integer,"
"PRIMARY KEY (field1, field2)"
");"

Answer 2

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