On 17 Aug 2014 12:05:08 -0700, "vogonjeltzprostet...@yahoo.com
[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.
