Re: unique & update

> On Tue, 2002-01-29 at 13:38, Ben-Nes Michael wrote:
> > Hi All
> >
> > 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
> > );
>
> O.K. ... we used a column constraint ...
>
> > I inserted 3 rows with value: 1, 2, 3
>
> ... everything is unique, so all is fine ...
>
> > now im trying to update:
> > update areas set lft = lft +1;
>
> ... update works on the first row, so the table would be 2, 2, 3 ...
>
> > Then I get the UNIQUE problem.
>
> ... what is exectly what the unique constraint is for.
>
> > Is there a way to do UNIQUE check after the whole table got updated ?
> > If not, any ideas to walk around the problem ?
>
> Try:
> CREATE TABLE areas (
> UNIQUE ( lft ),
> lft INT
> );

I tried your syntax and the following with no success ( same problem)
CREATE TABLE areas (
lft INT,
CONSTRAINT testunique UNIQUE ( lft )
);

>
> Then we have a table with a table constraint.
> I'm only guessing ( using ``\h CREATE TABLE'' on the psql prompt ) and
> I'm a PostgreSQL -- and in fact SQL -- newbie, but if I'm interpreting
> the syntax diagram and MY mind about these constraints right, this
> should do what you need.
>
> Regards
> Frank