I want to hold ordering of some items in SQL, like field in a table
and I have operation
If I want to move item from
index: 1 to
index: 3 I can do
delete from table where index = 1; update table set index = index - 1 where index > 1 update table set index = index + 1 where index >=3 insert into table (index) values (3)
from first sight all is ok, but we live in real world and if anything have no constraint it will be broken.
I know I can add constraints that index is unique, that index is not null, but as I know I can't add constraint that index should have no holes.
With holes in index i.e
index: 0,1,4,5 simple algorithm above is broken.
So the question is - may be other simple algorithm exists for holding item ordering?
(The problems with google search that
order is SQL keywords)