Обсуждение: unique constraint when updating tables

Hello,
    I met the following problem when i am using PostgreSQL as a backend database:
    I have a table "tabrel(a int primary key, b int)", and now there are for example 100 records with column 'a' be from 1 to 100. When i'm going to add one to each 'a' and intended to get 'a' varing from 2 to 101, i use SQL query as "update tabrel set a=a+1" , but i got " ERROR: duplicate key violates unique constraint tabrel_a_key".
    In my experience with using Oracle, SQL Server and Mysql, such SQL should be executed succussfully. After i met this problem, i turned back to some textbook about database transaction and i got information like this "it is ok to have some inconsistent state during one transaction, only if it is consistent after the transaction is finished succesfully." 
    And now, i'm wondering what's the problem with this SQL query? How 'update' is executed in the backend?
   
    I'm using PostgreSQL 8.1.4.

    Thanks
    Shawn Gao                                   
    2006-08-22

am  Tue, dem 22.08.2006, um  8:39:00 +0800 mailte gao iqiang folgendes:
> Hello,
>     I met the following problem when i am using PostgreSQL as a backend
> database:
>     I have a table "tabrel(a int primary key, b int)", and now there are for
> example 100 records with column 'a' be from 1 to 100. When i'm going to add one

Why do you want to do this?


> to each 'a' and intended to get 'a' varing from 2 to 101, i use SQL query as
> "update tabrel set a=a+1" , but i got " ERROR: duplicate key violates unique
> constraint tabrel_a_key".

You can do something like this:

test=# create table foo (id int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
test=# insert into foo values(1);
INSERT 0 1
test=# insert into foo values(2);
INSERT 0 1
test=# insert into foo values(3);
INSERT 0 1
test=# update foo set id = id+1;
ERROR:  duplicate key violates unique constraint "foo_pkey"
test=# begin;
BEGIN
test=# update foo set id = id+100;
UPDATE 3
test=# update foo set id = id-99;
UPDATE 3
test=# commit;
COMMIT
test=# select * from foo;
 id
----
  2
  3
  4
(3 rows)



Other solution:

test=# begin;
BEGIN
test=# alter table foo drop constraint foo_pkey;
ALTER TABLE
test=# update foo set id = id+1;
UPDATE 3
test=# alter table foo add primary key (id);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
ALTER TABLE
test=# commit;
COMMIT


HTH, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

On Tue, Aug 22, 2006 at 08:39:00AM +0800, gao iqiang wrote:
> Hello,
>    I met the following problem when i am using PostgreSQL as a backend
> database:
>    I have a table "tabrel(a int primary key, b int)", and now there are for
> example 100 records with column 'a' be from 1 to 100. When i'm going to add
> one to each 'a' and intended to get 'a' varing from 2 to 101, i use SQL
> query as "update tabrel set a=a+1" , but i got " ERROR: duplicate key
> violates unique constraint tabrel_a_key".

Yes, PostgreSQL doesn't currently support deferred unique constraints.

You can find workarounds in the archives.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.