Re: unique & update

On Tuesday 29 January 2002 19:13, Stephan Szabo wrote:
> 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.
Will 7.2 support it ?
If not do you have estimate time to when it will be supported ?

Can you tip me how to create trigger thats do uniqueness check ?

>
> 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;)