On Sep 4, 2008, at 8:56 PM, Darren Duncan wrote: > D. Richard Hipp wrote: >> 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. > > Is there any reason why you can't just optimize this away at the > parser > level by ignoring the UNIQUE keyword and only make the PRIMARY KEY > index?
Yes. It would result in an incompatible file format. Older versions of SQLite would be unable to read newer databases because they would be expecting to find multiple indices instead of just one. If I had discovered this problem before the file format was frozen, I could have fixed it. But it is too late now. > > (Or alternately to disallow those 2 terms appearing together?) Does > having > identical UNIQUE and PRIMARY KEY constraints lead to different > semantics > than having just the PRIMARY KEY? I suggest changing SQLite about > this. -- > Darren Duncan > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp [EMAIL PROTECTED] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
