How to use CREATE TABLE ... AS with Primary Key in SQLite From SQLite documentation for CREATE TABLE http://www.sqlite.org/lang_createtable.html:


  A table created using CREATE TABLE AS has no PRIMARY KEY and no
  constraints of any kind.


So is there any general way to create table with primary key &amp; other index information ?
I suspect you're missing the difference between CREATE TABLE and CREATE TABLE AS (otherwise known as CTAS).

CREATE TABLE AS allows you to create a table from the resultset of a query.

For example:

CREATE TABLE PHILSUCKS AS ( SELECT PHIL, SUCKS FROM EGGS );


You could, instead of using CTAS, use a "normal" CREATE TABLE statement, then INSERT the rows manually. This allows you to specify the PRIMARY KEY and any constraints. eg:

CREATE TABLE PHILSUCKS
(
  PHIL   INTEGER PRIMARY KEY,
  SUCKS  INTEGER NOT NULL
);

INSERT INTO PHILSUCKS ( SELECT PHIL, SUCKS FROM EGGS );


Obviously, you can also create indexes etc too:

CREATE INDEX EGGSUCKING ON PHILSUCKS (SUCKS);


Hope that helps!