Search code examples
sqliteinsert-updateupsert

Correct usage of the SQLite ON CONFLICT clause

I have a SQLite database which, amongst other things, has the following table.

CREATE TABLE IF NOT EXISTS biases
(
 data INTEGER NOT NULL,
 link INTEGER DEFAULT 0,
 bias_type INTEGER,
 ignores INTEGER DEFAULT 0,
 desists INTEGER DEFAULT 0,
 encashes INTEGER DEFAULT 0, 
 accesses INTEGER DEFAULT 0,
 scraps INTEGER DEFAULT 0,
 CONSTRAINT pk_bias_mix PRIMARY KEY(data,link,bias_type)
);

The constraint pk_bias_mix is being used to ensure that no two rows can have the same values for all three columns data, link and bias_type columns. So suppose I do

INSERT INTO biases (data,link,bias_type,ignores) VALUES(1,1,1,1);

things work as expected - a new row is inserted in the table. If I issue the same INSERT again I get the error

UNIQUE CONSTRAINT FAILED: biases.data,biases.link,biases.bias_type

just as expected. I tried to use the SQLite ON CONFLICT clause thus

INSERT INTO biases (data,link,bias_type,ignores) VALUES(1,1,1,1)
ON CONFLICT(data,link,bias_type) DO UPDATE SET ignores = ignores + 1;

and it worked as I had hoped - instead of adding a new row or throwing up an error SQLite incremented the value of the ignores column in the row with the matching data, link and bias_type values.

However, this is just the result of an experiment. It is not immediately clear to me from the SQLite docs that this is indeed how ON CONFLICT is supposed to behave - i.e it can be given two or more conflict constraints to be checked. What I mean by two or more constraints is specifying multiple, comma separated, columns inside CONFLICT(...) as I have done in the example above.

I suspect that this is the right usage since I am merely specifying a CONFLICT condition that replicates my indicated CONSTRAINT. However, I cannot see this explained explicitly anywhere in the docs. I'd be much obliged to anyone who might be able to confirm this.

Solution

  • From UPSERT:

    UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint.

    and:

    The special UPSERT processing happens only for uniqueness constraint on the table that is receiving the INSERT.

    So the DO UPDATE part is not triggered by any constraint conflict but only by a unique constraint violation.

    Also:

    The syntax that occurs in between the "ON CONFLICT" and "DO" keywords is called the "conflict target". The conflict target specifies a specific uniqueness constraint that will trigger the upsert.

    So it is not possible to have two or more conflict constraints to be checked in one statement.
    However you can use separate UPSERT statements to check for 2 different unique constraint violations.

    See a simplified demo where I added 1 more UNIQUE constraint to your table:

    CONSTRAINT con_scraps UNIQUE(scraps)