Menu
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.
922
A table can contain only one primary key constraint.
You can only have one primary key. From the MySQL documentation: A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently).
A primary key is the column or columns that contain values that uniquely identify each row in a table. A database table must have a primary key for Optim to insert, update, restore, or delete data from a database table.
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).
125
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).
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
