Re: changing multiple pk's in one update

>
> Date: Mon, 13 Apr 2009 17:09:49 -0400
> From: Glenn Maynard <glennfmaynard(at)gmail(dot)com>
> To: pgsql-sql(at)postgresql(dot)org
> Subject: Re: changing multiple pk's in one update
> Message-ID: <d18085b50904131409g10d43d6cs35dd14ede13bc02(at)mail(dot)gmail(dot)com>
>
> (JMdict? I was playing with importing that into a DB a while back,
> but the attributes in that XML are such a pain--and then my email died
> while I was trying to get those changed, and I never picked it up
> again.)
>
> On Mon, Apr 13, 2009 at 1:20 PM, Stuart McGraw <smcg2297(at)frii(dot)com> wrote:
>
>> > 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.
>>
>
> Your PK is a composite of (entry, order)? Won't your foreign keys
> elsewhere all break when you shift the order around?
>
>
>> > 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.
>>
>
> Why not do this in the straightforward way: three separate fields: a
> regular, sequential PK; an FK to the entry; and an order number. Add
> an index on (entry_key, order_number). It's a little more expensive
> since you have a new column and index (the PK), but in a table with a
> lot of plain text that's probably insignificant. Now you can use the
> plain PK for your FK's.
>
>
I'd agree with this approach. I have a number of tables which are
sensitive to arbitrary ordering and they sound roughly similar to your
use-case (though my tables are probably smaller).

My approach is to create a string column in the table which permits
defining arbitrary ordering. I use a string field b/c it's easier for me
to stuff (by hand) new ordered records in between other existing
records. But an integer would work just as well, so long as you make
sure you keep enough space between the integers (e.g. 1000, 2000, 3000).

Also, if your ordered list is truly "ordinal" (each record is either
1st, 2nd, 3rd, etc in a single list) you could just use 1,2,3,4 for the
ordering, but then you have to mess with two records in order to swap
the positions of (say) item 2 and 3. Of course you can do this pretty
easily inside a transaction, and you don't have to worry about the mess
of moving PK's.

Steve