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

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