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? (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
