Script categories

Thursday, 22 September 2011

Creating a reverse index primary key

-- Creating a reverse index primary key

create table t1 (a number, b number);
create unique index t1_idx on t1 (a,b) reverse;

alter table t1 add constraint t1_pk primary key (a,b) using index t1_idx;

/*
Creating a descending index is different to a reverse index.
Descending indexes are function-based using SYS_OP_DESCEND.
Function based indexes cannot be applied to primary keys
*/

SQL> create table t1 (a number, b number);

Table created.

SQL> create unique index t1_idx on t1 (a desc, b desc);

Index created.

SQL> alter table t1 add constraint t1_pk primary key (a,b) using index t1_idx;
alter table t1 add constraint t1_pk primary key (a,b) using index t1_idx
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

No comments:

Post a Comment