I have not tested but may be useful



Create a trigger before insert/update

New.col1=upper(new.col1);



Create an unique index for col1





_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of ygboro
Sent: Thursday, August 09, 2007 3:25 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Make a column unique and case insensitive



Hello,
please, how to make a varchar type column unique and case insensitive ?
I tried to use unique constraint:
CREATE TABLE TEST (COL1 VARCHAR(4) NOT NULL);
ALTER TABLE TEST ADD CONSTRAINT UNQ1_TEST UNIQUE (COL1)
USING INDEX UNQ1_IDX_COL1;
But such constraint is case sensitive and allows both 'abcd' and
'ABCD' to be stored.

Then I tried unique expression index instead of unique constraint:
CREATE UNIQUE INDEX TEST_IDX1_COL1 ON TEST COMPUTED BY (UPPER(COL1));
To my surprise this index acts like a constraint watching for
uppercase duplicity of incomming values. I used FB 2.0.1

Is unique expression index a valid way to enforce uniqueness and case
insensitivity for a column ? Are there better ways how to do it ?

Boro



__________ NOD32 2445 (20070808) Information __________

This message was checked by NOD32 antivirus system.
http://www.eset.com



[Non-text portions of this message have been removed]