Create conditional unique constraints on multiple columns

Question

I need to create a conditional unique constraint on multiple columns using Oracle but I am failing to do so.

Below is what I have right now but it's not working:

Basically, my conditional unique constraint needs to handle a combination of columns under one condition which a = 1.

alter table <table_name> add constraint <constraint_name> unique (<column_1>, <column_2>, a = 1);

I get this error when I run this alter statement:

ORA-00907: missing right parenthesis

As far as I can tell, that won't work as you planned. Perhaps enforcing it with a database trigger. Though, you didn't say what if a <> 1. Some sample data which shows valid and invalid rows might help someone to help you. — Littlefoot
As littlefoot said. If a is also a field you cant do that in a constraint. — Juan Carlos Oropeza
a is a column in the same table. If that cannot be done, then how else can I go about this? perhaps unique index with case statements? — samg
Your requirement is not clear, I don;t know what do you want to achieve, but you can replace a = 1 with case when a = 1 then 1 else 0 end or case when a = 1 then 1 end. The first version allows for only one record a=1 and only one record a<>1, the second version is less restrictive - allows for only one record a=1 and multiple records a<>1. — krokodilko
@krokodilko please see answer below and let me know what do you think? — samg

samg samg · Accepted Answer · 2019-06-04T20:38:32

I believe this is sufficient:

create unique index <index_name> on <table_name> (case when a = 1 then <column_1> end, 
                                                  case when a = 1 then <column_2> end);

Create conditional unique constraints on multiple columns

1 Answers