SQLite: Increment unique integer field

I have a table which contains a unique integer field containing consecutive values. When I try to increment these values using the method below I violate the unique constraint. Is there a method for doing this successfully?

CREATE TABLE numbers(num INT UNIQUE 开发者_开发百科NOT NULL)
UPDATE numbers SET num=num+1

---

This has to be a bug. It obviously won't induce a constraint violation, and it works in SQL Server. In fact, I am quite certain it is a bug, since I can make it succeed if I insert the numbers in descending order:

sqlite> INSERT INTO numbers (num) VALUES (3);
sqlite> INSERT INTO numbers (num) VALUES (2);
sqlite> INSERT INTO numbers (num) VALUES (1);
sqlite> UPDATE numbers SET num = num + 1;
sqlite> SELECT * FROM numbers;
4
3
2

The correctness of an UPDATE should not depend on the order of rows in the table.

As a simple work-around, you could do this:

UPDATE numbers SET num = -num;
UPDATE numbers SET num = 1 - num;

---

You should be able to achieve this by:
- finding the maximum value max_pk
- updating all rows with pk = pk + max_pk.
- updating all rows with pk = pk - max_pk + 1

---

The behaviour is correct. Consider the case where num was the referenced column in a foreign key constraint and updates were cascading. Any method of achieving the update other than updating through a cursor (including allowing deferral of integrity checking until commit time) which violates the unique constraint in an interim update would result in an inconsistent database (loss of integrity). For example, if a row with num = n+1 were updated following the update of a row with num = n, for any n. There are ways to "re-phrase" the update to avoid this, but it requires domain knowledge and therefore the engine cannot do it for you. Nor should it.

Create table numbers (num int unique);
Create table others (a int, num int unique references numbers (num) on update cascade);
Insert into numbers values (1), (2), (3), (4);
Insert into others values (1,1), (2,2), (3,3), (4,4);

Then any interim update to num which violated the unique constraint would result in integrity loss and the update should fail irrespective of row order. And you ought not depend on row order unless explicitly stated in the SQL statement. Turning off constraint checking (or deferring it until commit time) places the consequence of "not knowing what you are doing" directly in the hands of the programmer (where it belongs). If you know enough about the database to disable the integrity checking, you ought to be struck with the consequences (if any).

The only way to do the update without such side-effects is to ensure that the update does not have any interim (row by row) violations of integrity.

In some other engines you would use:

update numbers set num = num + 1 from numbers order by num desc;

to control the row processing order by performing the update though "current of cursor", which would always obtain the correct result every time.

Perhaps a useful enhancement would be to permit an update to use from and order by clauses thus allowing such an update to be expressed directly. Effectively, such an update would become a "select" where instead of returning rows, the "return a row" would be replaced with the update operation...

Some refer to this as an updateable view. It really isn't. It is still an update of "current of cursor", the update just takes place for each valid result row in a result set by allowing additional tables to be joined into the cursor, rather than restricting the cursor to only the single updated table.

You can achieve this presently by creating a view with the correct ordering, and then an update trigger for the num column on the view which updates the underlying table, then performing the update against the view:

Create table numbers (num int unique);
Create table others (a int, num int unique references numbers (num) on update cascade);
Insert into numbers values (1), (2), (3), (4);
Insert into others values (1,1), (2,2), (3,3), (4,4);
Create view updatenumbers
as 
    select num from numbers order by num desc;
    Create trigger updnum instead of update of num on updatenumbers 
begin 
    update numbers set num = new.num where num=old.num; 
end;

update updatenumbers set num = num + 1;

sqlite> select * from numbers; select * from others;
2
3
4
5
1|2
2|3
3|4
4|5

To be totally correct you should use the rowid to perform the update on the underlying table. The creation of the view to implement the cursor together with the instead-of trigger to update the underlying table based on the rowid then becomes a generic pattern to implement a "where current of cursor" style update. The trigger could then be made generic enough (if necessary) that it would work for updating any column or combination of columns through the cursor based on any selection of cursor rows ... and all referential constraints would still be maintained.

create table numbers (num int unique);
create table others (a int, num int unique references numbers (num) on update cascade);
insert into numbers values (1), (2), (3), (4);
insert into others values (1,1), (2,2), (3,3), (4,4);

create view updatenumbers
as 
    select numbers.rowid, * 
        from numbers 
order by num desc;

create trigger updnum instead of update of num on updatenumbers 
begin 
    update numbers 
        set num = new.num 
    where rowid=old.rowid;
end;

update updatenumbers set num = num + 1;

select * from numbers; 
select * from others;

2
3
4
5
1|2
2|3
3|4
4|5