CODEWITHSUNDEEP
database-designprimary-keyfirebird
dthrasher
dthrasher

Reputation: 41812

What is the maximum size of a primary key in Firebird?

I need to set a Varchar(255) field as the primary key of a database table in Firebird 2.1.

I get error messages saying that the field size is too large. I'm using UTF8 as my character set and the default page size of 4096.

Is it possible to do this in Firebird? I need to ensure that this column is unique.

Upvotes: 5

Views: 2857

Answers (3)

EMBarbosa
EMBarbosa

Reputation: 1493

As explained the maximum key size is 1/4 of the page size, but from Firebird Language Reference (here), the maximum indexable string length in bytes is 9 less than the maximum key length. And UTF8 in Firebird is stored internally as 4 Bytes/char.

Thus the maximum length for a UTF8 in a 4096 page sized database is 253 chars (4096/4 -9 = 1024 -9 = 1015 limiting to 253*4=1012). So, if you want a bigger string, you need a bigger page sized database (even if you are using Firebird 2.5.x).

Upvotes: 8

Hugues Van Landeghem
Hugues Van Landeghem

Reputation: 6808

just make gstat d:\path\database.fdb

you will get someting like this for your index :

> Index CLIENT_IDX (2) 
>         Depth: 3, leaf buckets: 545, nodes: 138523 
>         Average data length: 10.12, total dup: 13873, max dup: 645 
>         Fill distribution: 
>              0 - 19% = 0 
>             20 - 39% = 0 
>             40 - 59% = 0 
>             60 - 79% = 0 
>             80 - 99% = 545

if Depth is more than 3 : you will have to increase the page size. It is the same as Douglas Tosi say.

You can see this very well in tools like IBExpert full edition or in IBAnalist

Upvotes: 0

Douglas Tosi
Douglas Tosi

Reputation: 2350

According to FirebirdFAQ the maximum key size in Firebird 2.x is one fourth of the page size. If your page size is 4096 bytes your maximum key size is 1024 bytes.
UTF8 varchars reserve a full 32-bits per char even though it may use less space. Thus a varchar(255) in UTF8 is 1020 bytes. I don't know why it's hitting the limit, but anyway I'd increase the page size or try varchar(254).

Upvotes: 1

Related Questions