Are you migrating an existing database from Paradox, or are you working on a new Firebird database?

I am just learning Firebird, and I do not have the solution perse, but maybe these are going to help you or others a bit.

- Make sure your database is using the right character set for your data. When you create the database chose the correct one, or if you are working with an existing one with data do this on a (development) copy.

alter character set xxx; /* such as UTF8  */

If you receive an error with a non-empty database your data probably contains non-conforming characters for the chosen set already. I am not sure, I am only suspecting it.

Then in the future in Firebird you might also want to use domains to describe your data fields, such as:

create domain “UDB$SUBSIDIARY_NAME” char(50)

character set yyy, /* when it is different from the database default */

check (/* add rules */);

You may add collation rules, default values and other rules and validations. Then when you create a data tables with the name of the domain:

create table “CORPORATTION” ( “SUBSIDIARY_NAME” “UDB$SUBSIDIARY_NAME”);

you may rest assured that any other data field created in other data tables using UDB$SUBSIDIARY_NAME are having the exact same data types and validation rules.

Domains also nicely self-document your database, and once you resolve the issues you are now working on you are ready to re-use the solution just by typing a domain name when you create new data fields.

Of course this works best with new databases, and I just started using Firebird...

I hope this helps somewhat,

Sandor

 

This was something I would resort to years ago in Paradox.  Suppose I had a table CORPORATION with columns S_KEY INTEGER, SUBSIDIARY_NAME CHAR(50) and a table EMPLOYEE with columns CORPORATION_S_KEY INTEGER, NAME CHAR(50).  S_KEY is meant to be a surrogate key column with a PRIMARY KEY constraint on it.  CORPORATION_S_KEY is a foreign key referencing S_KEY in CORPORATION.  CORPORATION_S_KEY and NAME jointly have a primary key constraint on them.I don't want case-variants in the SUBSIDIARY_NAME column: if I've got 'Acme Corp.', I want to forbid 'ACME CORP.'.  Simple enough in Paradox: case-insensitive, unique index on SUBSIDIARY_NAME.  And, if I recall correctly, not bad in Firebird: unique index on lower(SUBSIDIARY_NAME).  I'm good for the first table.The problem comes with the second.  (1, 'Bob Jacobs'), (2, 'Bob Jacobs'), (3, 'BOB JACOBS'), and (4, 'bob jacobs') constitute an acceptable set of rows; (2, 'Bill Hafner') and (2, 'BILL HAFNER') do not.  That is, given a value for CORPORATION_S_KEY, there shouldn't be case-variants for the NAME value.  In Paradox, still simple enough to achieve the result: case-insensitive, unique, multi-column index on CORPORATION_S_KEY and NAME.  For Firebird . . . uh . . . . The first time I tried to address this concern, I don't remember whether I posted a question to the group.  What I do recall is creating a unique index on a maddening, blinding CASE expression.  It essentially involved a reinterpret-cast of the CORPORATION_S_KEY to char(4) character set octets concatenated with an appropriate cast of NAME to character set octets.  If the 4-octet prefix of the expression (the reinterpret-cast of CORPORATION_S_KEY) was the same, difference should be entirely determined by the trailing octets.  It worked as reliably as the Paradox solution in my tests.  But the expression was _hideous_: bit-shifts, bit-ands, ascii_char calls casted to char(1) character set octets, concatenations, casts, concatenations, casts, lather, rinse, repeat.  That prefix part, in particular, was a beast.  The expression was (almost) as daunting as that email-validation regex sometimes posted (as a joke, perhaps?) in various tech forums.  Actually, the integer column was a BIGINT, so multiply at least part of the misery by 2.I really do want to effect the constraint in question.  Is there a less abominable way to do it?  It'd really be nice if I missed something simple . . . . -Marc Benedict