Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9625
  • Last Modified:

How to resolve "ORA-01450: maximum key length (6398) exceeded" error?

I am facing the below error when I try to create primary key constraint on table_a.

"ORA-01450: maximum key length (6398) exceeded"

table_a structure is below,

create table table_a
(
  col_a        VARCHAR2(32 BYTE),
  col_b        VARCHAR2(32 BYTE),
  col_c        VARCHAR2(3 BYTE),
  col_d        DATE,
  col_e        VARCHAR2(3000 BYTE),
  col_f         VARCHAR2(4000 BYTE),
  col_g        DATE
)


Ran below sql statement and got an error,


ALTER TABLE table_a ADD (
  CONSTRAINT PK_table_a
 PRIMARY KEY
 (col_a, col_b, col_c, col_e, col_f)
    USING INDEX
    TABLESPACE WEBRPT_INDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

I checked max length by running below sql statement,

select max(length(col_a||col_b||col_c||col_e||col_f))
from table_a;

Looks like max length is 323. Not sure why I get above error. The question is how I resolve this issue. Please advise.
0
IT_ETL
Asked:
IT_ETL
  • 3
  • 2
  • 2
  • +1
5 Solutions
 
slightwv (䄆 Netminder) Commented:
Since some the columns 'can' contain up to 3000 and 4000 bytes, this is the problem.

Check out:
http://ora-01450.ora-code.com/


Do you really not have a natural primary key without those large columns?
0
 
sdstuberCommented:
the total "possible" length of entire key (all columns in the index) must be less than 6398

your key is potentially 7067 bytes long (plus a little for header information)
0
 
sdstuberCommented:
what version of the db are you using?

if 12c you can use the new hash and substr options to get a smaller key.

if 11g or lower you can create a unique index on the concatenation (subject to 4k limit) or some other function; and declare the columns not null; but it won't really be the same thing as a primary key
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
IT_ETLAuthor Commented:
"Do you really not have a natural primary key without those large columns? "

No, Should I add sequence number as part of primary key constraint instead of two large columns.

"what version of the db are you using?"

Oracle 10g


"if 11g or lower you can create a unique index on the concatenation (subject to 4k limit) or some other function; and declare the columns not null; but it won't really be the same thing as a primary key"

As you said, it won't really be useful.
0
 
slightwv (䄆 Netminder) Commented:
>>Should I add sequence number as part of primary key constraint instead of two large columns.

Adding a new column (sequence of GUID) seems like the only solution you have if you cannot change the design to have a natural primary key.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Having a column in the primary key with byte sizes of 3000 or 4000 .. looks like a bad design with no doubt. i have not seen such thing in my experience.

I believe you need to have the design set right first by identifying which are those columns which make up the primary key, may be using oracle sequences can be an option but you should think in those lines.

Assuming you were able to setup that primary key without errors - how are you going to use that and how beneficial is that going to be in terms of performance etc are some of the questions which might come up post that right.

May be you need to throw some light on your actual requirement and i hope you get some good feedback from experts.

Thanks,
0
 
slightwv (䄆 Netminder) Commented:
I agree that it appears to be a 'bad' design.

I would also like to learn more about the actual system/data/???

IT_ETL, if you can provide more about the data without posting any 'trade secrets', I'm sure we can help.

Typically a date, down to the second, is good enough for use in a primary key along with some additional  id.  When it isn't, then look to a timestamp column which supports fractional seconds.

In the database I support, even this isn't enough but I was lucky, I could add a sleep for .01 seconds per transaction to ensure a different timestamp.

If you cannot afford a fractional second delay, then you might go with a sequence or guid.  I do this in some tables as well.

I am interested what in your 3000 and 4000 byte columns actually make a record unique and suitable for a primary key.

You might get by with a lookup 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 in the primary key.
0
 
IT_ETLAuthor Commented:
Thank you all for posting your comments.

I do agree it was a bad design. But I have modified the design, added a new dimension table, a new key field (from dimension table) has been added to the fact table along with other fields col_a, col_b, and col_c will be part of the primary key constraint in a fact table.

So, the issue has been resolved as I don't need to use below fields to create a primary key constraint anymore.
 
col_e  VARCHAR2(3000 BYTE),
col_f         VARCHAR2(4000 BYTE)
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now