CODEWITHSUNDEEP
sqloracle-databaseconstraintsunique
samg
samg

Reputation: 323

Create conditional unique constraints on multiple columns

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

Upvotes: 0

Views: 376

Answers (1)

samg
samg

Reputation: 323

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);

Upvotes: 1

Related Questions