ORA-14071: invalid option for an index used to enforce a constraint

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This error seems to be the Oracle XE equivalent of ORA-25176 that is apparently returned on Oracle standard editions when you execute a DDL statement like:

CREATE TABLE test_table (
key_a varchar2(10),
key_b number(5,0),
key_c number(10,0),
col_d varchar2(30),
PRIMARY KEY (key_a, key_b, key_c) USING INDEX COMPRESS
) ORGANIZATION INDEX
/

ORA-14071: invalid option for an index used to enforce a constraint

As explained in a previous posting about ORA-00439, it's perfectly possibile to specify the COMPRESS option for an Index Organized Table (IOT), however you must specify this clause in the right place:

CREATE TABLE test_table (
key_a varchar2(10),
key_b number(5,0),
key_c number(10,0),
col_d varchar2(30),
primary key(key_a, key_b, key_c)
) ORGANIZATION INDEX
COMPRESS
/

See message translations for ORA-14071 and search additional resources