Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How long is long enough for primary key ?
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