--- In firebird-support@yahoogroups.com, "h_urlaf" <h_urlaf@y...> wrote:

> Martijn Tonies wrote:
> > To index those columns, you would have to reduce their size to
> > VARCHAR(84) or less. CHAR is not a good type for variable data or
> > for wide indexes, because it pads the data out to full width.
>
> Dropping the size to 80 does work, thanks!

Unfortunately, dropping the size to 80 chars is not an option in our
data model. Drat.

Would replacing the UNIQUE constraint with something like below in a
BEFORE INSERT OR UPDATE trigger do the job? Or would there be a chance
of race conditions (seems to me like there might be).

CREATE TRIGGER "TB$DOCUMENT" FOR DOCUMENT
ACTIVE BEFORE INSERT OR UPDATE
DECLARE VARIABLE NEW_NAME VARCHAR(255);
DECLARE VARIABLE OLD_ID BIGINT;
AS
BEGIN
IF (INSERTING) THEN
BEGIN
OLD_ID = NULL;
IF (new.ID IS NULL) THEN new.ID = gen_id(G$DOCUMENT_ID,1);
END

IF (UPDATING) THEN
BEGIN
OLD_ID = old.ID;
END

if (EXISTS(SELECT * FROM DOCUMENT WHERE
ID <> :OLD_ID
AND DEVICE_ID = new.DEVICE_ID
AND USER_ID = new.USER_ID
AND NAME = new.NAME
AND COALESCE(MIME_TYPE, 'application/pdf') =
COALESCE(new.MIME_TYPE, 'application/pdf')
)) THEN EXCEPTION E$DUPLICATE_NAME;

/* UNIQUE (USER_ID, DEVICE_ID, NAME, COALESCE(MIME_TYPE,
'application/tiff')) */
END ^