Unique Key Question

Dear Informixers, I have a table with a composite unique key, composed of three columns: team_id, week, position "position" contains consecutive numbers for each combination of "team_id" and "week". Lets say for a given week and team_id, I have 5 rows at positions "1" to "5". Now I want to insert a new row at position "3", so the positions of the previous entries >= 3 have to be incremented by 1 to make room for the new row. I cannot just issue update <table> set position = position + 1 where team_id = <value> and week = <value> because this gives me a unique key violation. Of course I can achieve what I want in a program or procedure, where I increment starting with the highest position and counting my way down. But is there a way to do this in plain SQL? Regards, Richard

Richard Spitz wrote: > Dear Informixers, > > I have a table with a composite unique key, composed of three > columns: team_id, week, position > > "position" contains consecutive numbers for each combination of > "team_id" and "week". Lets say for a given week and team_id, > I have 5 rows at positions "1" to "5". Now I want to insert a > new row at position "3", so the positions of the previous > entries >= 3 have to be incremented by 1 to make room for the > new row. > > I cannot just issue > update <table> set position = position + 1 > where team_id = <value> and week = <value> and position >= 3, surely? > because this gives me a unique key violation. Of course I can > achieve what I want in a program or procedure, where I increment > starting with the highest position and counting my way down. But > is there a way to do this in plain SQL? > Ok, it tries to update position 3 to 4, but 4 already exists. You need it to update 5->6, 4->5, etc. so that it leaves holes. Maybe it'll work if you make the postion part of the key descending? The only other thing I can think of, assuming you don't normally have negative position, is to renumber your existing positions but make them -ve, insert the new row and then make the -ve rows +ve again, ie. update <table> set position = (position + 1) * -1 where team_id = <team> and week = <week> and position >= 3; insert into <table> values(<team>, <week>, 3, <data>); update <table> set week = week * -1 where team_id = <team> and week = <week> and position < 0;

may the thing you want..... create table xxa ( a int, b int , primary key ( a,b));-- where primary key is your -- unique index.... -- you may use a unique -- constraint instead...... -- it will fail when primary key -- is changed into a unq index insert into xxa values ( 1,1); insert into xxa values ( 1,2); insert into xxa values ( 1,4); insert into xxa values ( 1,5); begin work; set constraints all deferred; update xxa set b = b + 1 where b > 2 Superboer.