One occasionally sees SQLite schemas of the following form:
CREATE TABLE meta(id LONGVARCHAR UNIQUE PRIMARY KEY, ....);
In other words, one sometimes finds a PRIMARY KEY and a UNIQUE
declaration on the same column. This works fine in SQLite, but it is
wasteful, both of disk space and of CPU time. If we ignore the
INTEGER PRIMARY KEY exception, the "PRIMARY KEY" keyword in SQLite
means the same thing as "UNIQUE". Both create a unique index on the
column. So if you use them both on the same column, you get two
identical unique indices. SQLite will dutifully maintain them both -
requiring twice the CPU time and twice the disk space. But having a
redundant index does not make queries run any faster. The extra index
merely takes up time and space.
So here is a good rule of thumb: Never use both UNIQUE and PRIMARY
KEY on the same column in SQLite.
D. Richard Hipp
[EMAIL PROTECTED]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
