[SOLVED] Conditional unique index in Firebird

Conditional unique index in Firebird

In SQL Server I can do this

CREATE UNIQUE INDEX [IX_MyTable_Reference]
    ON [dbo].[MyTable] ([Reference])
    WHERE [Expired] = 0

Is there an equivalent in Firebird?

Solution

Firebird 4.0 and older unfortunately do not. Firebird 5.0 does have conditional (or partial) indexes, see also Does Firebird support filtered indexes? and Partial Indexes (not partitioning) [CORE3384] #3750 and the Firebird 5.0 Release Notes).

I can think of two possible alternatives:

Use a unique computed index that stores Reference when Expired is 0 and null otherwise:
```
create unique index "IX_MyTable_Reference" 
  on "MyTable" 
  computed by (iif("Expired" = 0, "Reference", null));
```
This index will enforce the uniqueness, but can only be used as an index itself in queries that literally use iif("Expired" = 0, "Reference", null) = <somevalue>, which makes it more complicated and less intuitive to use as an index compared to a partial index (where you'd be able to use "Expired" = 0 and "Reference" = <somevalue>).
Use a trigger that populates a separate table that can enforce the uniqueness through a unique constraint (or index) and a trigger that removes the record on delete (or use a cascading foreign key constraint) or if the Expired value changes.

This solution doesn't give the benefits of having the index when querying (except maybe by explicitly joining the additional table).

Given the complexity of this solution, I leave this as an exercise to the reader.

Other potential alternatives like using a check constraint or trigger to check the contents of the current table wouldn't prevent concurrent transactions from inserting duplicates, nor do you get the benefits of having a partial index.