?
Solved

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

Posted on 2013-11-05
8
Medium Priority
?
8,222 Views
Last Modified: 2013-11-20
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
Comment
Question by:IT_ETL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1200 total points
ID: 39624480
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 400 total points
ID: 39624500
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39624507
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:IT_ETL
ID: 39625048
"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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1200 total points
ID: 39625068
>>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
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 400 total points
ID: 39626134
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
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1200 total points
ID: 39626205
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
 

Author Comment

by:IT_ETL
ID: 39664040
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question