Key size too big for index

Hello everybody!!


I was trying to define the index for my table PRODUCTS using theese fields:

COMPANY_CODE VARCHAR (2)
REF_NUMBER VARCHAR (256)

COMPANY_CODE VARCHAR (2)

and i can't because this error


SEVERE 27/07/11 15:36:liquibase: Error executing SQL ALTER TABLE PRODUCTS ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (COMPANY_CODE, REF_NUMBER)
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544351. unsuccessful metadata update

My database has utf8 charset and has a page size of 16384

According to volny index calculator:

i could create this index:

Number of columns 2
Total key size 1292
Remains 2800

¿Why can't i do it?


Regards

I think you need to post the current DDL for the table as reported by Firebird

The isql with the sql output is this:

isql

connect INVENT.FDB user 'XXX' password 'XXX';

SQL> ALTER TABLE PRODUCTS ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (COMPANY_CODE, REF_NUMBER)
Statement failed, SQLCODE = -607

unsuccessful metadata update
-key size too big for index PRODUCTS
SQL>

Not how you connect to the database - @clivew wants the table structure- i.e CREATE TABLE PRODUCTS ( .....

Not how you connect to the database - @clivew wants the table structure- i.e CREATE TABLE PRODUCTS (Fields, data types etc)

The create table sql is t:

create table PRODUCTS
(
COMPANY_CODE VARCHAR(2) not NULL,
REF_NUMBER VARCHAR(256) not NULL,
DESC VARCHAR(100),
);

You are still not posting the FULL DDL for the table (with all its constraints etc.) as REPORTED BY Firebird.

I can tell this because:
1. What you posted would not compile due to the comma before the closing paren.
2. There is no mention of the PRODUCTS index referenced in your error message.

Trust me, we are not just being picky to make your life difficult.
We give of our time freely and we need to know what Firebird itself reports NOT what you think you instructed it to create in order to use our time wisely.

Clive

Sorry , i have made the two mistakes writing the ddl sentences due to have been copied by hand.

The complete ddl sentences are theese:

create table PRODUCTS
(
COMPANY_CODE VARCHAR(2) not NULL,
REF_NUMBER VARCHAR(256) not NULL,
DESC VARCHAR(100)
);

ALTER TABLE PRODUCTS ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (COMPANY_CODE, REF_NUMBER)

Statement failed, SQLCODE = -607

unsuccessful metadata update
-key size too big for index PRODUCTS_PK

The table has not more constraints, only this pk.

Regards.

Sorry , i have made the two mistakes writing the ddl sentences due to have been copied by hand.

The complete ddl sentences are theese:

create table PRODUCTS
(
COMPANY_CODE VARCHAR(2) not NULL,
REF_NUMBER VARCHAR(256) not NULL,
DESC VARCHAR(100)
);

ALTER TABLE PRODUCTS ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (COMPANY_CODE, REF_NUMBER)

Statement failed, SQLCODE = -607

unsuccessful metadata update
-key size too big for index PRODUCTS_PK

The table has not more constraints, only this pk.

Regards.

I have tested and it works on firebird 2.5 page size 16384
with db UTF-8

ODS Version 11.2
Page Size 16384
Pages 153
Size on Disk 2.39MB
SQL Dialect 3
Default Character Set UTF8

https://gist.github.com/1113493

Please do a backup restore and check if it works after that
ps: also according to index calculator should be enough space for the index
http://www.volny.cz/iprenosil/inter...xcalculator.htm

I know what is happening:

I am using latest fyracle version which is based in firebird 1.5 version.

I though this fyracle version was based in latest firebird version (2.5) and it is not true.

Whith the 1.5 version and according to wolny index key size calculator the index is too big.

Thanks to all for the help and time used.

Glad to hear you are up and running.

No doubt you will now understand the importance of giving us a complete picture when soliciting our help.

I don't believe you ever mentioned fyracle and your failure to cut/paste information we requested accurately
led us down some blind alleys.

This is not a complaint; just a lesson for anyone who reads this post about the importance of
giving us a complete context to get a rapid solution.