Thread

update on unique constraint

Theo Kramer <theo@flame.co.za> — 2000-11-22T17:07:49Z

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