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

update on unique constraint

From: Theo Kramer <theo(at)flame(dot)co(dot)za>
To: pgsql-bugs(at)postgresql(dot)org
Subject: update on unique constraint
Date: 2000-11-22 17:07:49
Message-ID: 200011221707.TAA27495@flame.flame.co.za (view raw or flat)
Thread:
Lists: pgsql-bugs
postgresql 7.0.3 on RedHat 7.0
------------------------------

create table whois (domain text, version integer);
CREATE
theo=# create unique index whois_idx on whois (domain,version);
CREATE
theo=# insert into whois values ('mydomain.co.za', 0);
INSERT 18829 1
theo=# insert into whois values ('mydomain.co.za', 1);
INSERT 18830 1
theo=# insert into whois values ('mydomain.co.za', 2);
INSERT 18831 1
theo=# update whois set version = version + 1 where domain = 'mydomain.co.za';
ERROR:  Cannot insert a duplicate key into unique index whois_idx

Oracle 8.1.6 on RedHat 6.2
--------------------------
SQL> create table whois (domain varchar2(64), version integer);

Table created.

SQL> create unique index whois_idx on whois (domain,version);

Index created.

SQL> insert into whois values ('mydomain.co.za', 0);
1 row created.

SQL> insert into whois values ('mydomain.co.za', 1);

1 row created.

SQL> insert into whois values ('mydomain.co.za', 2);

1 row created.

SQL> update whois set version = version + 1 where domain = 'mydomain.co.za';

3 rows updated.

SQL> select * from whois;

DOMAIN                                   VERSION
---------------------------------------- ----------
mydomain.co.za                           1
mydomain.co.za                           2
mydomain.co.za                           3

SQL>

Ideas/thoughts?

I think oracle is correct - no contraints are violated and the update
should be atomic.

Regards
Theo

pgsql-bugs by date

Next:From: Matt BenjaminDate: 2000-11-23 01:09:07
Subject: STDC_HEADERS on Solaris 8
Previous:From: aixitDate: 2000-11-22 16:10:59
Subject: error weil dumping

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