On Thu, Oct 20, 2011 at 09:02:55AM -0700, Sean Pieper scratched on the wall: > I apologize for the newbie question, but the answer isn't obvious from > looking through the site. > > In postgres, I know that if I declare a column or set of columns to > have a unique constraint, there is also an index created on those > columns. Does the same thing happen in sqlite, or does the > optimization engine see the unique constraint as a hint to > dynamically create an "automatic" index if existing indices > seem insufficient?
There are some SQLite-specific issues concerning "INTEGER PRIMARY KEY" columns (which, in PostgreSQL speak, will automatically map to the OID column), but the general answer is "yes", and automatic index is implied: http://sqlite.org/lang_createtable.html SQL Data Constraints [...] INTEGER PRIMARY KEY columns aside, both UNIQUE and PRIMARY KEY constraints are implemented by creating an index in the database (in the same way as a "CREATE UNIQUE INDEX" statement would). Such an index is used like any other index in the database to optimize queries. As a result, there often no advantage (but significant overhead) in creating an index on a set of columns that are already collectively subject to a UNIQUE or PRIMARY KEY constraint. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
