Clarification, please

In Oracle, deferrable primary keys are enforced by non-unique indexes.
That seems logical, because index should tolerate duplicate values for
the duration of transaction:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application
    Testing options

    SQL> create table test1
      2  (col1 integer,
      3   constraint test1_pk primary key(col1) deferrable);

    Table created.

    Elapsed: 00:00:00.35
    SQL> select uniqueness from user_indexes where index_name='TEST1_PK';

    UNIQUENES
    ---------
    NONUNIQUE

PostgreSQL 9.0, however, creates a unique index:

    scott=# create table test1
    scott-# (col1 integer,
    scott(#  constraint test1_pk primary key(col1) deferrable);
    NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
    "test1_pk" for table "test1"
    CREATE TABLE
    Time: 67.263 ms
    scott=# select indexdef from pg_indexes where indexname='test1_pk';
                             indexdef
    ----------------------------------------------------------
     CREATE UNIQUE INDEX test1_pk ON test1 USING btree (col1)
    (1 row)

When the constraint is deferred in the transaction block, however, it
tolerates duplicate values until the end of transaction:

    scott=# begin;
    BEGIN
    Time: 0.201 ms
    scott=# set constraints test1_pk  deferred;
    SET CONSTRAINTS
    Time: 0.651 ms
    scott=# insert into test1 values(1);
    INSERT 0 1
    Time: 1.223 ms
    scott=# insert into test1 values(1);
    INSERT 0 1
    Time: 0.390 ms
    scott=# rollback;
    ROLLBACK
    Time: 0.254 ms
    scott=#


No errors here. How is it possible to insert the same value twice into a
UNIQUE index? What's going on here?


--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions