Re: changing multiple pk's in one update

Scott Marlowe wrote:
> 2009/4/7 Stuart McGraw <smcg2297(at)frii(dot)com>:
>> Hello all,
>>
>> I have a table with a primary key column
>> that contains sequential numbers.
>>
>> Sometimes I need to shift them all up or down
>> by a fixed amount. For example, if I have
>> four rows with primary keys, 2, 3, 4, 5, I
>> might want to shift them down by 1 by doing:
>>
>
> Generally speaking, when you need to do this more than once or twice
> in the lifetime of your data, there's something wrong with your data
> model.

Generally speaking, I agree. But in this case I am not sure
how to do it any better. Simplified, the database models
a dictionary. An entry in the dictionary can have a number
senses where each sense is a sentence that gives the meaning
of the sense. The order of the senses is significant. The
sense table has three columns, an fk to the dictionary entry
(id number) it is part of, a sense number (small number from
1 to the number of sentences in the entry) and the sentence
text. The pk is of course the entry id and the sense number.
There are other tables that have fk's to the senses.

I could dispense with the sentence number and use the
sentence text as the second part of the composite key but
the sentence text is far more volatile than the number, and
can be very long making it quite unwieldy to use as a key.

I guess I could add an "order"[1] column and use the sense number
as a surrogate partial key to avoid the need for key renumbering,
but all the api's (and the normal human way of thinking) are based
on "sense number 1 of entry x", "sense number 2 of entry y", so
one would need to maintain "order" as a gapless sequence (or add
a new mapping layer to map from/to a arbitrary monotonic sequence
to a 1,2,3,... sequence) -- the gain doesn't seem that big.

Or maybe there is some other approach?

[1] yes, I know "order" is a reserved word.