• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Firebird RDBMS Primary key size

 
Ranch Hand
Posts: 166
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am converting an oracle database schema to the firebird server. When I add a primay key for one of the existing tables a get a key too large error. Does anyone know the limitation on firebird primary (unique) constraints?
 
Ranch Hand
Posts: 214
IntelliJ IDE Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The limitation would be the data type of the underlying column(s) of the primary key. The more recent versions of Firebird/Interbase/Phoenix support 64-bit integers. (I'm assuming your primary key column is an integer type.)

Oracle is a little bit different with its numeric datatypes. The ANSI datatype INTEGER in Oracle is a synonym for NUMBER(38), which represent a number with up to 38 digits, which can overflow even a 64-bit integer type. The Interbase INTEGER type is 32-bit.

Firebird/Interbase supports up to 18 digits of exact numerics with the NUMERIC(18) data type, if you use SqlDialect 3. NUMERIC(18) is equivalent, at least approximately, to a 64-bit integer.

Do you have more than a 64-bit integer's worth or rows in your tables? If the primary key column in your Firebird database is NUMERIC(18) and your Oracle keys are still too big, you have a problem.

Assuming the original range of keys is sparse, you could to devise a scheme to regenerate the primary keys in Firebird, while maintaining your FK constraints of course. You could also try defining your primary key as two columns of NUMERIC(18) and split the original key in half when you insert.
 
Jeremy Wilson
Ranch Hand
Posts: 166
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
My problem is the legacy application has primary keys made up of 5 columns of varchars that range from 16 to 256. I have almost had knock down fights in trying to get this changed but it never happened. I know the best idea would be to a single integer or uuid as the primary key but no luck in that solution. I need to know what and preferrably why the limitations are on firebird. This may be a show stopper in using firebird as an open source alternative if it cannot be worked around.
 
Edwin Keeton
Ranch Hand
Posts: 214
IntelliJ IDE Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yikes!

The max index size for Interbase/Firebird is 252 bytes, for a single column index. For multiple columns it's even smaller and complicated to figure out, but you're not going to fit your 5 columns into 252 bytes anyway.

If you don't have any flexibility to re-design this index, you won't be able to use Firebird.

Rumor has it that the next big update to Firebird will eliminate this restriction, but the best I can tell, the next release will not be anytime soon. In the meantime PostgreSQL seems to be eating Firebird's lunch. You might want to take a look at it if you can't use Firebird.
 
Jeremy Wilson
Ranch Hand
Posts: 166
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Looks like I Firebird may be out to lunch again then, will probably move on to postgres
 
reply
    Bookmark Topic Watch Topic
  • New Topic