SQLite Forum

slow import of CSV files
> What is the difference between PRIMARY KEY and a separate UNIQUE index?

There is almost no difference between a PRIMARY KEY and a UNIQUE index (whether that UNIQUE index is specified via the UNIQUE constraint in a table definition or by a separate CREATE UNIQUE INDEX statement) with the following exceptions:

 - INTEGER PRIMARY KEY in a RowID table (that is, a table that is not created WITHOUT ROWID) declares an explicit name for the rowid.  
 - if the table is the target of a REFERENCES clause that does not specify the column(s) referenced in the target, then the PRIMARY KEY column(s) are assumed.  
 - expressions can be used in the index expression list of a CREATE UNIQUE INDEX statement but cannot be used in a PRIMARY KEY or UNIQUE declaration in a table specification.

Other than these extremely specific points of functionality, PRIMARY KEY is merely syntactic sugar (an alternate spelling) for UNIQUE in the table definition, and merely an alternate declaration method (syntactic sugar) for a CREATE UNIQUE INDEX statement.