SQLite Forum

unique column constraint vs unique index

unique column constraint vs unique index

(1) By Mark Wagner (markxwagner) on 2020-10-02 17:33:47 [source]

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?


(2) By Keith Medcalf (kmedcalf) on 2020-10-02 21:42:44 in reply to 1 [link] [source]

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

(3) By anonymous on 2020-10-03 00:40:10 in reply to 1 [link] [source]

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.

(4) By Keith Medcalf (kmedcalf) on 2020-10-03 04:06:47 in reply to 3 [link] [source]

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.

(5) By anonymous on 2020-10-05 03:44:00 in reply to 4 [link] [source]

I don't think you can set the index to automatically use the on conflict criteria though, you can only specify on the insert statement.