Re: unique & update

On Tue, 29 Jan 2002, Ben-Nes Michael wrote:

> Im into SQL trees trying to work with CELKO way to do it:
> http://www.intelligententerprise.com/001020/celko.shtml
>
> The problem is that if I try to add a new descendent which is not the most
> right sibling I get UNIQUE error.
>
> This occur ( if im right ) when the update try to update the lft column
> which is UNIQUE
>
> Example of table:
>
> CREATE TABLE areas (
> lft INT UNIQUE
> );
>
> I inserted 3 rows with value: 1, 2, 3
>
> now im trying to update:
> update areas set lft = lft +1;
>
> Then I get the UNIQUE problem.
>
> Is there a way to do UNIQUE check after the whole table got updated ?
> If not, any ideas to walk around the problem ?

Unfortunately no, we don't support deferred unique constraint afaik and
the unique constraint we have isn't quite correct according to spec (yes,
the above should work). Technically we should be checking the after all
updates have occurred, but iirc we do it on the insert into the index
which is why this happens.

The closest thing I could think of would be a constraint trigger that did
a uniqueness check but that'll probably be somewhat slower (a trigger that
looks for something like: select lft from areas group by lft having
count(*)>1;)