At 03:33 AM 23-06-01 +0300, you wrote:

>How can we have records sorted alphabetically with case insensitivity in
>IB6?
>Is it possible to enforce case insensitive uniqueness in a field?

Add an extra column (proxy column for the column on which you want the case-insensitive attributes). Make sure the original column has a NOT NULL constraint on it.

ALTER TABLE MYTABLE
ADD MyCol2 VARCHAR(n)
NOT NULL UNIQUE
COLLATE <collation order>;

Create Before Insert and Before Update triggers to populate the column:

..
CREATE TRIGGER POP_MYCOL2_BI
FOR MYTABLE
ACTIVE BEFORE INSERT AS
BEGIN
MYCOL2 = UPPER(MYCOL);
END

CREATE TRIGGER POP_MYCOL2_BU
FOR MYTABLE
ACTIVE BEFORE UPDATE AS
BEGIN
IF (NEW.MYCOL <> OLD.MYCOL) THEN
NEW.MYCOL2 = UPPER(NEW.MYCOL);
END

If you have existing data, populate the new column with a DSQL statement UPDATE MYTABLE SET MYCOL2=UPPER(MYCOL);
Note that you will get constraint violation errors if you have any existing data in MyCol in different rows, that would uppercase to an identical string, e.g. MacDonald and Macdonald.

To get your case-insensitive ordering, just ORDER BY the proxy column.

Cheers,
Helen


All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________