create table soft_delete_test
( id number primary key
, first_name varchar2(100) not null
, surname varchar2(100) not null
, deleted_ind varchar2(1) check (deleted_ind = 'Y')
)Table created.
create unique index soft_delete_test_name_uk on soft_delete_test
( nvl2(deleted_ind,null,first_name)
, nvl2(deleted_ind,null,surname)
)Index created.
insert into soft_delete_test (id,first_name,surname) values (1,'Jeff','Kemp')1 row(s) inserted.
insert into soft_delete_test (id,first_name,surname) values (2,'Kane','Barnes')1 row(s) inserted.
insert into soft_delete_test (id,first_name,surname) values (3,'Lara','Kraft')1 row(s) inserted.
insert into soft_delete_test (id,first_name,surname) values (4,'Jeff','Kemp')ORA-00001: unique constraint (SQL_ULTZWCNBWUBVRDBHBGILAIOCV.SOFT_DELETE_TEST_NAME_UK) violated ORA-06512: at "SYS.DBMS_SQL", line 1721More Details: https://docs.oracle.com/error-help/db/ora-00001
update soft_delete_test set deleted_ind = 'Y' where id = 11 row(s) updated.
insert into soft_delete_test (id,first_name,surname) values (4,'Jeff','Kemp')1 row(s) inserted.
update soft_delete_test set deleted_ind = '' where id = 1