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