Re: unique constraint

On Oct 20, 2010, at 15:58 , Jamie Kahgee wrote:

> I have a table of paragraphs for pages that are in a specific order (1st,
> 2nd, 3rd, etc...).

<snip />

> I tried using a unique constraint on the page/pos columns, but was running
> into constraint errors when I did an update to move positions - in a
> transaction, there might be two pages at the same position for an instance
> while they are getting shuffled around.
>
> to fix this I tried deferring the constraints, but as we're using version
> 8.2.5, realized this isn't supported and might not be the best approach?

Upgrade if you can. Otherwise you can do two updates when rearranging paragraphs. The first updates them to an "invalid" range which isn't used in "correct" data and so won't conflict with other values, and the second moves them back to the valid range of positions.
Negative numbers are often used for this middle range.

This is basically a hierarchy problem. Take a gander around the web for nested sets for examples.

Michael Glaesemann
grzm seespotcode net