Many thanks. I think my confusion was from the overloading of "automatic index" to mean both "implicit, static" and "generated at runtime"
-sean -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Jay A. Kreibich Sent: Thursday, October 20, 2011 10:31 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] does a unique constraint imply an index 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 ----------------------------------------------------------------------------------- This email message is for the sole use of the intended recipient(s) and may contain confidential information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. ----------------------------------------------------------------------------------- _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
