There are two kinds of design of deletion in an enterprise application:

• Soft Delete
• Hard Delete

Soft Delete does not delete the record from database, but just set a flag to mark it as deleted. On the contrary, Hard Delete deletes the record from database, so there is no way to get it back after deletion.

Either design has some pros and cons. Relatively, Soft Delete is quite complicated for implementation and maintenance. But why people still choose this design? One good reason is for auditing. Auditing is usually a mandatory requirement for an enterprise application.

But Soft Delete caused many problems, data integrity is significant one, especially unique constraint.

For example, table color:

create table color ( ID integer, color VARCHAR2(10), isdelete CHAR(1) default 'N');

isdelete is used for Soft Delete.

Because when isdelete is ‘Y’, there could be more than one records has the same ID. So we cannot use unique constraint on ID column.

A possible solution is use Oracle Function-Based unique index:

create unique index color_unique_index on color (NVL2(NULLIF(isdelete, 'Y'), ID, null));

Here I use to functions:

NULLIF

The syntax for the NULLIF function is:

    NULLIF( expr1, expr2 )

expr1 and expr2 must be either numeric values or values that are of the same data type.

NVL2

The syntax for the NVL2 function is:

    NVL2( string1, value_if_NOT_null, value_if_null )

string1 is the string to test for a null value.

value_if_NOT_null is the value returned if string1 is not null.

value_if_null is the value returned if string1 is null.

With this unique index, I can insert only one active record for each ID, but can insert any number of “deleted” records with same ID.