Skip site navigation (1) Skip section navigation (2)

Re: unique & update

From: "Ben-Nes Michael" <miki(at)canaan(dot)co(dot)il>
To: "Frank Schafer" <frank(dot)schafer(at)setuza(dot)cz>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: unique & update
Date: 2002-01-29 13:52:18
Message-ID: 00c801c1a8cc$2a96b880$aa0f5ac2@canaan.co.il (view raw or flat)
Thread:
Lists: pgsql-general
> 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



In response to

Responses

pgsql-general by date

Next:From: Juan Carlos Moscardó PérezDate: 2002-01-29 14:28:57
Subject: Re: unique & update
Previous:From: Frank SchaferDate: 2002-01-29 13:25:43
Subject: Re: unique & update

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group