On 17 Aug 2014 12:05:08 -0700, "vogonjeltzprostetnic@...
[firebird-support]" <firebird-support@yahoogroups.com> 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

For creating a case insensitive multi-column index you need to define the
varchar column with a case insensitive collations, if you then include the
column in an index it will be case insensitive as well:

CREATE TABLE caseinsensitive
(
id integer NOT NULL,
theColumn VARCHAR(200) CHARACTER SET UTF8 NOT NULL COLLATE
UNICODE_CI_AI,
CONSTRAINT UK_caseinses_theColumn UNIQUE (id, theColumn)
);

That is all you need (although you may need a locale specific case
insensitive collation instead of the general UNICODE_CI_AI, which BTW is
also accent insensitive).

PS Next time state your question/problem first and then explain what you
tried before, I had a hard time understanding what you wanted.