Re: unique & update

----- Original Message -----
From: "Ben-Nes Michael" <miki@canaan.co.il>
To: "Frank Schafer" <frank.schafer@setuza.cz>;
<pgsql-general@postgresql.org>
Sent: Tuesday, January 29, 2002 2:52 PM
Subject: Re: [GENERAL] 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>