Using a Primary Key with a WithoutRowID

Question

I am trying to squeeze every little bit of performance out of SQLite and I have a problem which seems to be odd, in that the functionality seems pointless in SQLite.

Take for example:

CREATE TABLE "A_TEST" ( "ID" INTEGER PRIMARY KEY ,  "X" TEXT NULL) WITHOUT ROWID

then try to insert a record:

Insert into A_TEST (X) VALUES('Test String')

You will get an error of "NOT NULL constraint failed"

Does this mean, with a WithoutRowID, I have to specify my own Primary Key Value when inserting?

The reason why I think the WithoutRowID is pointless is that:

1. You have to specify your own Primary Key Value which makes any mass insert select statement redundant as I would have to specify my own value in the primary key when inserting....

2. I will in effect, have 2 primary keys if I don't use WithoutRowID, because SQLite manages its own RowID as well as my own Primary Key value. On the 1.7GB database, having WithoutRowID reduces the size of the indexes in the file to just 1.3GB so 400MB difference is pretty huge savings.

Please tell me that I don't have to provide my own Primary Key ID and that it will in fact provide a Unique ID against a Primary Key if it is an INTEGER.

Answer 1

and I just found that SQLite 4 is available - and it looks (from initial reading of the web page) that Primary Keys will indeed be REAL primary keys without the ROWID! Whoop Whoop. http://sqlite.org/src4/doc/trunk/www/design.wiki