Wednesday, January 23, 2008

ORA-25176: storage specification not permitted for primary key

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

This error message can be returned when an unexpected clause is added in a primary constraint specification for an index organized table:
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-25176: storage specification not permitted for primary key
Two facts must be noted here:
the "USING INDEX" clause above becomes perfectly legitimate if we remove the COMPRESS keyword or if we move it after the ORGANIZATION INDEX clause, as already explained in a similar situation occurring in Oracle XE, where a different error message is returned (see ORA-14071).
Likewise, if you remove ORGANIZATION INDEX, the COMPRESS clause will be accepted without problems:
CREATE TABLE test_table (
key_a varchar2(10),
key_b number(5,0),
key_c number(10,0),
col_d varchar2(30),
constraint pk_test_table primary key(key_a, key_b, key_c) USING INDEX COMPRESS 2
)
In conclusion, you just picked a wrong syntax, if you want to enable compression on an index organized table, then change the statement as follows:

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
) ORGANIZATION INDEX
COMPRESS -- see the note below
/
As already mentioned in a previous posting, you can also specify COMPRESS n, where n is a number lesser than the number of key columns and in this specific case you could specify either COMPRESS 1 or COMPRESS 2, because the key is made up of three columns.
See message translations for ORA-25176 and search additional resources

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles