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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
