samedi 26 septembre 2015

Is there a way to "defer" unique constraints in sqlite?

I have a table in my sqlite database with a "position" column (for sorting/display order), and I want a UNIQUE constraint on it. But this constraint prevents me from updating multiple records in one query (for example, UPDATE things SET position = position + 1). Is there anything I can do to "defer" the constraint check until after the entire query has executed (sort of like how you can use DEFERRED on foreign key constraints)?

Sample code:

CREATE TABLE test (
  id integer PRIMARY KEY NOT NULL,
  position integer NOT NULL,
  title varchar(255) NOT NULL
);
CREATE UNIQUE INDEX unique_position ON test (position ASC);


INSERT INTO test (position, title) VALUES (0, 'first item');
INSERT INTO test (position, title) VALUES (1, 'second item');
INSERT INTO test (position, title) VALUES (2, 'third item');

UPDATE test SET position = position + 1; -- <-THIS IS WHAT I'D LIKE TO BE ABLE TO DO

Aucun commentaire:

Enregistrer un commentaire