ORA-01450: maximum key length

create table ir_temp_feeload (s_category varchar2(800),s_cov varchar2(800),v_rate varchar2(10) ) ;

 alter table ir_temp_feeload  add constraint uk_ unique(s_category,s_cov) ;

ORA-01450: maximum key length (6398) exceeded

This error is due to the bad database design
This error occurs as key length of index exceeds 6398

key length = sum of all column lengths in index + number of columns + 2 (key length) + 6 (restricted ROWID) + 1 (ROWID field length)


Here  = (800+800) + (3)+2+6+1 = 1612

select * from nls_database_parameters where parameter like '%SET%';

AL32UTF8 is a multi-byte character set with up to 4 bytes per character

So the key value =  1612*4 = 6448

 Solution :
---------

As a general rule, indexing on very large columns (raw, long, clob) is rarely useful because the optimizer will almost always find a full-table scan cheaper than invoking an index on a long column value.

It is better to create a look up table that holds all the distinct values for the large text with a number id. Then you have a foreign key in the main table or use it as primary key