I have seen frequent mention that the "most efficient" key for a
SQLITE3 table is to declare a column as "INTEGER UNIQUE". I know from
past experience that the primary key for any database table should
consist of the first and contiguous column(s) in the table.
Does SQLITE3 treat the following with the same degree of efficiency,
or will one result in better performance than the others?
1. CREATE TABLE Table1 (PKey INTEGER UNIQUE NOT NULL, Col2 VARCHAR
(32) NOT NULL, Col3 VARCHAR(64) NULL);
2. CREATE TABLE Table2 (PKey INTEGER NOT NULL PRIMARY KEY, Col2
VARCHAR(32) NOT NULL, Col3 VARCHAR(64) NULL);
3. CREATE TABLE Table3 (PKey INTEGER NOT NULL, Col2 VARCHAR(32) NOT
NULL, Col3 VARCHAR(64) NULL, PRIMARY KEY(PKey));
I have not used the first form, and the latter is the form that I
tend to use since it is the most 'universal' across different
database packages.
-ken
- [sqlite] PRIMARY KEY Issue Ken & Deb Allen
-