unique column constraint vs unique index
(1) By Mark Wagner (markxwagner) on 2020-10-02 17:33:47 [link] [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?
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.