At 10:02 AM 7/06/2003 +0000, you wrote:

>Hello All:
>
> I get this error when I try to create the following table, I have
>read that the size is limited to 252 but I think the one i am defining
>is shorter:
>
>CREATE TABLE test1
> (
> c_o VARCHAR(25) NOT NULL COLLATE ES_ES,
> c_m VARCHAR(25) NOT NULL COLLATE ES_ES,
> c_u VARCHAR(25) NOT NULL COLLATE ES_ES,
> CONSTRAINT pk_test1 PRIMARY KEY (c_o, c_m, c_u)
> );
>
>When I try to execute it I get:
>
>Statement failed, SQLCODE = -607
>
>unsuccessful metadata update
>-key size too big for index PK_TEST1
>
>SQL> show version;
>ISQL Version: LI-T1.5.0.3477 Firebird 1.5 Release Candidate 3
>Firebird/linux Intel (access method), version "LI-T1.5.0.3477 Firebird
>1.5 Release Candidate 3"
>on disk structure version 10.1
>
>how can I solve this problem?

Because of the three collations, your index size limit is drastically
reduced from the "standard" 252-byte limit.

3 * 25 characters of meaningful data doth not a good primary index make,
regardless of the size limitation. Apart from breaking the golden ACID
rule of making all keys free of any meaning as data, your PK will slow down
joins and referential integrity triggers. Under many conditions, it will
mess up the optimizer as well.

You are far better to make a surrogate primary key, using a generator and
triggers to make it auto-incrementing. This will solve the size limit
problem for the key and also prevent clutter in your foreign key and join
criteria bindings. Provide separate indexes with collations for each of
those key elements for use by the optimizer in searches and sorts.

You can place a UNIQUE constraint on the three columns WITHOUT COLLATIONS,
to prevent duplications.

Helen