Make a unique constraint deferrable

Is it possible to create an unique deferrable constraint enforced by an unique index? No, it's impossible! You can create an unique constraint with 'deferrable' state. You can create an unique constraint enforced by an unique index. But you can't have both.

Below statement will create an unique constraint enforced by an unique index by default.

21:36:13 SQL> alter table p_product_ce_determinants add constraint p_pced_uk unique
21:36:36 2 (charge_element_id, ce_det_version_no, display_order) using index;

Table altered.

This will create an unique constraint with 'deferrable' state enforced by an non-unique index by default.

21:39:29 SQL> alter table p_product_ce_determinants add constraint p_pced_uk unique
21:39:40 2 (charge_element_id, ce_det_version_no, display_order) deferrable using index;

Table altered.

You need to drop the constraint and the index separately.


You will get an error when trying to add an unique constraint with 'deferrable' state enforced by an existing unique index.

21:40:14 SQL> create unique index p_pced_uk on p_product_ce_determinants
21:40:44 2 (charge_element_id, ce_det_version_no, display_order);

Index created.

Elapsed: 00:00:00.03
21:40:49 SQL> alter table p_product_ce_determinants add constraint p_pced_uk unique
21:40:57 2 (charge_element_id, ce_det_version_no, display_order) deferrable using index p_pced_uk;
alter table p_product_ce_determinants add constraint p_pced_uk unique
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

Elapsed: 00:00:00.06

However, if you add the constraint solely enforced by the unique index, the statement will succeed.

21:41:08 SQL> alter table p_product_ce_determinants add constraint p_pced_uk unique
21:41:23 2 (charge_element_id, ce_det_version_no, display_order) using index p_pced_uk;

Table altered.

Elapsed: 00:00:00.01

You still need to drop the constraint and the index separately.


Instead the statement below will succeed and an unique constraint with 'deferrable' state will be created as enforced by an existing non-unique index.

21:42:17 SQL> create index p_pced_uk on p_product_ce_determinants
21:42:28 2 (charge_element_id, ce_det_version_no, display_order);

Index created.

Elapsed: 00:00:00.03
21:42:33 SQL> alter table p_product_ce_determinants add constraint p_pced_uk unique
21:42:37 2 (charge_element_id, ce_det_version_no, display_order) deferrable using index p_pced_uk;

Table altered.

Elapsed: 00:00:00.03

I tested in both 9i and 10g. While they have the same behavior, 10g gives more clear messages than 9i.