I've always assumed that a unique index on a single column was superior to a unique column constraint since the former can be dropped and/or recreated, etc (e.g. in the future I decide that really it should be unique across two columns or really it doesn't need to be unique at all).

Is there any downside to using a unique index in this way?  Is it really all the same under the covers?

Am I missing something?

Thanks.

Yes, they are the same.  A unique constraint in a table definition is exactly and precisely the same as an explicit unique index, save for what is different:

 - an explicit UNIQUE index may contain expressions, a UNIQUE constraint in a table may only contain bare columns  
 - you have control over the identifier name of an explicit UNIQUE index but have no control over the identifier name of a unique index created by a unique constraint  
 - you may drop or create an explicit UNIQUE index but you may not explicitly drop or create the index implicitly created by a UNIQUE constraint

I don't believe you can use the "on conflict" clauses on unique indexes like you can with column constraints, but you can still use "insert or ignore/replace" with the indexes.

Of course it can.  I assume that you mean the so-called upsert clause of an insert which specifies `INSERT ... ON CONFLICT (column-list) DO ...` as its syntax.

It makes absolutely no difference the fashion you use to define that column-list is unique, merely that it is.

This is specifically documented in the 5th paragraph of section 2 of <https://sqlite.org/lang_upsert.html> which states:

 > The special UPSERT processing happens only for uniqueness constraint on the table that is receiving the INSERT. A "uniqueness constraint" is an explicit UNIQUE or PRIMARY KEY constraint within the CREATE TABLE statement, or a unique index. UPSERT does not intervene for failed NOT NULL or foreign key constraints or for constraints that are implemented using triggers.