Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How long is long enough for primary key ?

Re: How long is long enough for primary key ?

From: The Flying Spontinalli <wolf__at_tiscali.co.uk>
Date: 8 Oct 2006 03:23:38 -0700
Message-ID: <1160303017.900862.323680@k70g2000cwa.googlegroups.com>

krislioe_at_gmail.com wrote:
> Hi,
>
> In our design, lookup tables (Customers, Products etc..) are using
> natural key as PK. We use VARCHAR2(30) as PK column.
>
> Is it too long ? How long is too long ?
>
> Thank you,
> xtanto

There is a maximum key length in Oracle.

If you try to add a primary key which is too long you'll get an ORA_1450: The following is a quote from metalink:

"When creating an Index, the total length of the index cannot exceed a certain value. This value depends primarily on the DB_BLOCK_SIZE. If an attempt is made to create an index larger than the Maximum value, an ORA-1450 is raised:

ORA-01450 maximum key length (758) exceeded  ->(2K Block)
ORA-01450 maximum key length (1578) exceeded ->(4K block)
ORA-01450 maximum key length (3218) exceeded ->(8K Block)
ORA-01450 maximum key length (6498) exceeded ->(16K Block)

The number in parends is the maximum allowable length of the index key for that
particular system.

So, how is this number calculated?

The maximum key size means:

The total index length + length of the key (2 Bytes) + ROWID (6 Bytes) +
the length of the rowid (1 byte)." Received on Sun Oct 08 2006 - 05:23:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US