Below example is from Oracle® Database Administrator's Guide 10g Release 2 (10.2) - Chapter 16 Managing Indexes
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/indexes.htm#i1006566
but the example code fails with ORA-14196;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as hr
DROP TABLE c PURGE ;
CREATE TABLE c(c1 INT, c2 INT);
CREATE UNIQUE INDEX ci ON c (c1, c2);
ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;
ORA-14196: Specified index cannot be used to enforce the constraint.
DROP TABLE c PURGE ;
CREATE TABLE c(c1 INT, c2 INT);
CREATE UNIQUE INDEX ci ON c (c1);
ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1, c2) USING INDEX ci;
ORA-14196: Specified index cannot be used to enforce the constraint.
DROP TABLE c PURGE ;
CREATE TABLE c(c1 INT, c2 INT);
CREATE UNIQUE INDEX ci ON c (c1, c2);
ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1);
SELECT index_name FROM user_indexes WHERE table_name = 'C';
INDEX_NAME
------------------------------
CPK
CI
Other than having two different indexes can there be any workarounds for ORA-14196 here? Because this is a need on a heavy insert/update table.
Best regards.