Conditional Unique Constraint

Imagine the following scenario:

You store information about different types of vehicles in a common table VEHICLES. The types of vehicles include CAR, BIKE and BOAT. Amongst other data, the name of the vehicle is stored.

Now the business wants you to make sure that the vehicle's name is unique for BOATs, but not for the other types.

We do not have the possibility to create a unique constraint in the database for this requirement. So, what can we do? Writing trigger logic is an option, but not very easy to implement, not very performant, and hidden.

There is a better option! A unique, function based index!

We can take advantage of the fact that Oracle does not store null values in an index.

Let's go:

create table vehicles
(vhc_id   number(38,0)  not null
,vhc_type varchar2(5)   not null
,vhc_name varchar2(100)
);

create unique index uk_vhc_name 
on vehicles
(case
   when vhc_type = 'BOAT' then vhc_name
   else null
 end
);

Now let's see if that works. We should be able to have two cars with the same name:

insert into vehicles values (1, 'CAR', 'My car.');
insert into vehicles values (2, 'CAR', 'My car.');

Yup, that works.

But we should not be able to have two boats with an identical name:

insert into vehicles values (3, 'BOAT', 'My boat.');
insert into vehicles values (4, 'BOAT', 'My boat.');

Yes, we made it! The second insert yields in "ORA-00001: unique constraint (UK_VHC_NAME) violated".