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 On 08/17/2014 02:05 PM, [email protected] [firebird-support] wrote: > > 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 > >
