2
create table RHP_EmployeElement (
    amount double precision not null, 
    comment varchar(255), 
    loan blob, 
    element_codeId varchar(30), 
    empPrd_emp_code varchar(255), 
    empPrd_emp_folder_codeId numeric(18,0),
    empPrd_prd_exe_exercice integer, 
    empPrd_prd_exe_fdr_codeId numeric(18,0), 
    empPrd_prd_period integer, 
    primary key (element_codeId,
        empPrd_emp_code, empPrd_emp_folder_codeId, empPrd_prd_exe_exercice,
        empPrd_prd_exe_folder_codeId, empPrd_prd_period)
)

Firebird 2.5.2 (with 4096 pages size, UTF8 charset) generates the exception :

org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544351. unsuccessful metadata update key size exceeds implementation restriction for index "RDB$PRIMARY43"

I also encountered same limit exception about Column Name size(31 chars i think)

All thoses limits are a hindrance to use Firebird in a professional Hibernate project, is it possible to rebuild Firebird project for Linux and Windows without all thoses Limits ?, or is there another way to break thoses limits.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Nassim MOUALEK
  • 4,702
  • 4
  • 25
  • 44
  • 1
    Indexes in Firebird have a limited size, the exact limitation depends on 1) the Firebird version and 2) the page size of your database. Please include the Firebird version, the page size and column definitions (datatypes, length, character sets (and database default character set)). BTW: Your primary key contains **6** columns, that seems a bit excessive. – Mark Rotteveel Feb 05 '14 at 13:10
  • this is done on purpose, i use embeded keys because i need to make sure that the code or the identity of some table must be accessible in other table, this prevent me to make a lot of join to get the wanted result, for simplification and optimisation – Nassim MOUALEK Feb 05 '14 at 13:24

1 Answers1

4

Indexes in Firebird 2.0 and higher are limited by the page size: an index key cannot exceed 1/4 of the page size. For a page size of 4096 this means the index key length is max 1024 bytes, for a page size of 16384, it is 4096 bytes.

Your primary key (and its backing index) consists of the following columns:

  • element_codeId VARCHAR(30) = 30 bytes (single byte charset) or 120 (UTF8)
  • empPrd_emp_code VARCHAR(255) = 255 bytes (single byte charset) or 1020 (UTF8)
  • empPrd_emp_folder_codeId NUMERIC(18,0) = 8 bytes
  • empPrd_prd_exe_exercice INTEGER = 4 bytes
  • empPrd_prd_exe_folder_codeId NOT LISTED, assuming it is empPrd_prd_exe_fdr_codeId NUMERIC(18,0) = 8 bytes
  • empPrd_prd_period INTEGER = 4 bytes

This leads to a total index key of 309 (single byte charset) or 1164 (+ some extra bytes overhead for multiple columns, collation etc). With character set UTF8 and page size 4096 this exceeds the 1/4 of page size rule. The solution is to use a bigger page size for your database, or to use a single byte character set for the VARCHAR columns.

As to the column names, the current limit for objectnames in Firebird 3.0 and earlier is 31 characters (which is similar to Oracle's limitation of 30 bytes). The feature request to extend this (CORE-749) has been implemented for Firebird 4. As with other database systems with this limitation, you will need to provide an explicit column name using for example @Column(name="theshortername"). Similar options exists for other objects.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • Thank you, very very mutch, empPrd_emp_code VARCHAR(255) should be VARCHAR(20) not 255, i think this should fix the problem – Nassim MOUALEK Feb 05 '14 at 13:36
  • @user3252285 I'd expect so, because that would result in an index key of 224 bytes (+ overhead). – Mark Rotteveel Feb 05 '14 at 16:56
  • in fact the target id was configured as 20 chars length, but the JPA generator wasn't able to retreive the correct length from the composite key, so i forced it with @JoinColumns annotation, without your help i thought that the problem was about the number of column index, not theire sum of size, so thanks again – Nassim MOUALEK Feb 05 '14 at 18:00
  • Mark Rotteveel , i dont know how to join you, but could you help me with this question please http://stackoverflow.com/questions/23180669/jaybird-hibernate-delete-where-in-clause-token-unknown – Nassim MOUALEK Apr 20 '14 at 09:50