What is the difference between CREATE INDEX AND CREATE UNIQUE INDEX?

Question

What is the difference between CREATE INDEX AND CREATE UNIQUE INDEX in SQLite3 used in Android OS? How do indexes work in database? How are they related to columns in the table?

Answer 1

CREATE UNIQUE INDEX isn't just an index : it adds the constraint that all records must have a different value for this column or combination of columns. If you try to insert a record whit the same combination, an error will be raised and prevent the insert (or update).

Use UNIQUE as soon as you're sure you'll have at most one record for some kind of key (user name for example).

EDIT : Here's a link to the documentation of CREATE INDEX, it describes in detail the syntax.