|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;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).
>(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.
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,