UPSERT On Conflict with no target

(1) By ddevienne on 2020-03-31 13:46:48 [link]

From [UPSERT doc](https://www.sqlite.org/lang_UPSERT.html)

> [...] The conflict target specifies a specific uniqueness constraint that will trigger the upsert.  
> The conflict target is required for DO UPDATE upserts, but is optional for DO NOTHING.  
> When the conflict target is omitted, the upsert behavior is triggered by a violation of **any**  
> uniqueness constraint on the table of the INSERT. [...]

My table has a single (3-column) UNIQUE constraint, and no other constraints.  
An `insert ... on conflict do update set ...` fails with a syntax error.  
While `insert ... on conflict(col1, col2, col3) do update set ...` is OK.

I'm obviously misreading the _When the conflict target is omitted_ sentence.  
What am I missing? That somehow applies to _DO NOTHING_ only? The previous  
sentence does say _required for DO UPDATE_, but the next one read independently to me.  

In any case, I find this confusing as written, FWIW. Thanks, --DD

(2) By David Raymond (dvdraymond) on 2020-03-31 14:19:34 in reply to 1

If you're going to do an update, then you need a specific constraint.

If you're going to do nothing, then you can either specify a constraint, or leave it blank to mean any constraint.

It's because one insert row could violate multiple different unique constraints, with it being a _different row_ for each of the constraints that's causing the violation, and the upsert is only intended to update a single row.

But if you're not going to update a row and just "do nothing" then it's ok to say "for any constraint", because the action would be the same for all of them without any ambiguity.