Subject Case-insensitive, unique, multi-column index
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