Re: 16 or 32 columns for a composite key in Oracle8.0.4? Which is right?

Hi.
It's definitely 32 in Oracle 8 - it used be 16 in Oracle 7.
However, note that you are also limited by the size of the fields in the index. The total length of the 32 fields cannot be greater than somewhere between one-third and one-half the block size being used.
Try, on a 2k block database to create an index on a single varchar2(1000) column -- it won't let you -- the error message will tell you the maximum length allowed.

Cheers
Coakleyj

In article <LcNk3.977$x7.18874827_at_nr1.ottawa.istar.net>,   "Zhenming" <zmwang_at_maloca.com> wrote:
> I find myself consusing with the reading Oracle8.0 manuals concering Oracle
> composite key maximum columns. I use the Oracle8.0.4 documentation.
>
> From Oracle8.0.4 concepts Manual: it says:
>
> "Oracle enforces unique integrity constraints with indexes. (In Figure 24-4,
> Oracle enforces the UNIQUE key constraint by implicitly creating a unique
> index on the composite unique key.) Therefore, composite UNIQUE key
> constraints have the same limitations imposed on composite indexes: up to 32
> columns can constitute a composite unique key, and the total size (in bytes)
> of a key value cannot exceed approximately half the associated database's
> block size. If a useable index exists when a unique key constraint is
> created, the constraint will use that index rather than implicitly creating
> a new one.
>
> Oracle enforces primary key constraints using indexes, and composite primary
> key constraints are limited to 32 columns, which is the same limitation
> imposed on composite indexes. The name of the index is the same as the name
> of the constraint. Also, you can specify the storage options for the index
> by including the ENABLE clause in the CREATE TABLE or ALTER TABLE statement
> used to create the constraint. If a useable index exists when a primary key
> constraint is created, the primary key constraint will use that index rather
> than implicitly creating a new one.
>
> Foreign keys can consist of multiple columns. However, a composite foreign
> key must reference a composite primary or unique key with the same number of
> columns and the same datatypes. Because composite primary and unique keys
> are limited to 32 columns, a composite foreign key is also limited to 32
> columns".
>
> While From Oracle8.0.4 SQL References: It says differently:
>
> "A composite unique key is a unique key made up of a combination of columns.
> Oracle creates an index on the columns of a unique key, so a composite
> unique key can contain a maximum of 16 columns. To define a composite unique
> key, you must use table_constraint syntax rather than column_constraint
> syntax.
>
> A composite primary key is a primary key made up of a combination of
> columns. Oracle creates an index on the columns of a primary key; therefore,
> a composite primary key can contain a maximum of 16 columns. To define a
> composite primary key, you must use the table_constraint syntax rather than
> the column_constraint syntax.
>
> A composite foreign key is a foreign key made up of a combination of
> columns. A composite foreign key can contain as many as 16 columns. To
> define a referential integrity constraint with a composite foreign key, you
> must use table_constraint syntax. You cannot use column_constraint syntax,
> because this syntax can impose rules only on a single column. A composite
> foreign key must refer to a composite unique key or a composite primary
> key".
>
> Which is right?
>
> Zhenming
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Jul 20 1999 - 07:31:04 CDT