Hello Community,
I wanted to use an index in a constraint when I encountered an error message:
ORA-14196 Specified index cannot be used to enforce the constraint
This sample created the error
CREATE UNIQUE INDEX MID ON MATABLE (MADATE DESC);
ALTER TABLE MATABLE ADD CONSTRAINT CPK UNIQUE (MADATE) USING INDEX MID;
That one worked:
CREATE UNIQUE INDEX MID ON MATABLE (MADATE ASC);
ALTER TABLE MATABLE ADD CONSTRAINT CPK UNIQUE (MADATE) USING INDEX MID;
ASC works, DESC does not
SQL Error: ORA-14196: Specified index cannot be used to enforce the constraint.
14196. 00000 - "Specified index cannot be used to enforce the constraint."
*Cause: The index specified to enforce the constraint is unsuitable
for the purpose.
*Action: Specify a suitable index or allow one to be built automatically.