Subject RE: [firebird-support] Case-insensitive, unique, multi-column index
Author Svein Erling Tysvær
>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.

There are at least 3 ways of solving this, the simplest (which I recommend if you don't need case sensitive sorting) would be to use a case insensitive collation for the field (exists for many character sets).

The second option is to use a computed index: CREATE UNIQUE INDEX I_TEST_NAME ON TEST computed by(ID||'::'|| upper(NAME)); or just a computed index that you use in a unique constraint.

The third option is to have an extra field that you populate through a BEFORE INSERT trigger: new.UC_Equivalent = upper(NAME).

There may exist other options as well,
Svein Erling