CODEWITHSUNDEEP
triggerssqlite
Knuckles
Knuckles

Reputation: 193

Sqlite trigger oddly fails on unique constraint

This is SQLite version 3.16.0 2016-11-04

Here's my test schema:

PRAGMA foreign_keys = ON;
CREATE TABLE A (a_id integer primary key, x int);
CREATE TABLE B (b_id integer primary key, a_id integer not null references A(a_id) ON DELETE RESTRICT ON UPDATE CASCADE, descr text);
CREATE TABLE C (id integer primary key, dt DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TRIGGER Tb after update on B begin
    replace into C (id) select NEW.b_id;
end;

Here's some test data (as per .dump):

INSERT INTO "A" VALUES(1, 5000);
INSERT INTO "B" VALUES(1, 1, 'none');

Now, if I manually update a row in table B, like so:

UPDATE B SET descr = "test1" WHERE b_id = 1;

I can see a new row (which gets updated every time I touch B) in table C:

1|2017-04-17 21:59:42

I can also manually "touch" C the same way it's done in the trigger:

replace into C (id) select 1;

As expected, the dt column gets updated. All good so far. But suddenly, if I change a_id, referenced from B:

UPDATE A SET a_id = 2 WHERE a_id = 1;

I get Error: UNIQUE constraint failed: C.id

In my understanding, the foreign key from B.a_id to A.a_id updates B's row. That leads to the trigger trying to touch C. Which then for some reason fails, though it just worked perfectly fine in previous scenarios.

Why does that happen and how can I fix it?

Upvotes: 1

Views: 583

Answers (1)

CL.
CL.

Reputation: 180070

You are using the REPLACE command, which

is an alias for the "INSERT OR REPLACE" variant of the INSERT command.

The trigger documentation says:

An ON CONFLICT clause may be specified as part of an UPDATE or INSERT action within the body of the trigger. However if an ON CONFLICT clause is specified as part of the statement causing the trigger to fire, then conflict handling policy of the outer statement is used instead.

So when the outer command is an UPDATE, your REPLACE also becomes an UPDATE.

As a workaround, replace the REPLACE with the appropriate DELETE and INSERT commands.

Upvotes: 1

Related Questions