Chris,


chrisacron schrieb:

> Hi All
>
> Please help a FireBird newbie. I want to create a constraint that
> allows duplicate null (or blank fields) but unique if there are
> characters in it.
>
> In other words as long as the user leaves the field blank, he can have
> many records that are blank. When he enters a value into the field it
> should make sure that that value is unique.

with FB 1.5 it is possible if you leave them NULL (not EMPTY STRINGS!)
(see the docs, they have examples).


If you can't use that one (pre FB1.5) you can use a workaround with a
second field that has the unique index on it and will hold either the
given value or otherwise produce a unique value (which can't be a
"given" one.


For example:
I do have a field SCANCODE which has to be unique within a table (if
there's a scancode available)

CREATE TABLE PACKAGE (
PACK_ID INTEGER NOT NULL,
PACK_SCANCODE VARCHAR(20),
PACK_SCANCODE_UNIQUE VARCHAR(50) NOT NULL,
CONSTRAINT PK_PACKAGES PRIMARY KEY ( PACK_ID );
);

CREATE UNIQUE INDEX IXQ_PACKAGE_SCANCODE
ON PACKAGES ( PACK_SCANCODE_UNIQUE );

And triggers to supply / maintain the unqiue value.

CREATE TRIGGER BI_PACKAGE
..
AS BEGIN

( IF NEW.PACKSCANCODE IS NULL OR NEW.PACK_SCANCODE = '' ) THEN
BEGIN
NEW.PACK_SCANCODE_UNIQUE =
'NO_REAL_SCANCODE_CAN_LOOK_LIKE_THIS'
|| CAST ( NEW.PACK_ID AS VARCHAR(12));
END
ELSE BEGIN
NEW.PACK_SCANCODE_UNIQUE = NEW.PACKSCANCODE;
END
END

etc.

HTH

Luc.