CODEWITHSUNDEEP
sqlitesql-updateunique
Spook
Spook

Reputation: 25929

How to temporarily break column uniqueness in SQLite?

I've got a simple table:

CREATE TABLE ChecklistStep (
    Checklist INTEGER REFERENCES Checklist (Id), 
    Id        INTEGER PRIMARY KEY AUTOINCREMENT, 
    StepIndex INTEGER NOT NULL, 
    Name      VARCHAR NOT NULL, 
    UNIQUE (Checklist, StepIndex));

Now I want to exchange two items' indexes. Doing so I'm breaking temporarily uniqueness of pair (Checklist, StepIndex). I hoped, that I can do that in transaction, such that after committing, all constraints will be kept, but this doesn't work:

begin transaction;
update ChecklistStep set StepIndex = 0 where id = 6;
update ChecklistStep set StepIndex = 1 where id = 5;
commit transaction;

Causes:

UNIQUE constraint failed: ChecklistStep.Checklist, ChecklistStep.StepIndex

How to write such update?

Upvotes: 5

Views: 1786

Answers (1)

CL.
CL.

Reputation: 180270

SQLite does not have deferred UNIQUE constraints.

The PRAGMA writable_schema dirty trick does not work here because the internal index would get corrupted if you changed the table without updating the index.

The only way to do this is to use a temporary value that is guaranteed to be unused:

begin;
update ChecklistStep set StepIndex = -99999999 where id = 6;
update ChecklistStep set StepIndex = 1 where id = 5;
update ChecklistStep set StepIndex = 0 where id = 6;
commit;

Upvotes: 7

Related Questions