Thanks for the question, Atharva.
Asked: October 23, 2025 - 9:30 pm UTC
Last updated: October 24, 2025 - 4:10 pm UTC
Version: 19c
You Asked
Hello experts,
I have a UNIQUE constraint on (col1, col2) for a table. But, due to new functionality, I need a conditional unique constraint based on col 3.
So something like this:
if col3 = 'Val1':
UNIQUE constraint on (col1, col2)
else:
UNIQUE constraint on (col4, col5)
I'm on oracle DB 19c, and found that creating a unique index with case type helps. Can you guide me on alternative options and the pros, cons for this design. I do not want to create any new table and want the best way to achieve conditional unique constraints.
Thanks
and Chris said...
If you need conditional uniqueness, then using case expressions in a unique index is the way to do this.
We've not got much to work with here, so it's hard to give any meaningful recommendations though.
I do not want to create any new table
Why?
Two completely independent unique constraints which are mutually exclusive strongly suggests to me that these columns belong in separate tables.
Give us more background about what you're trying to do and why you've taken this approach and we'll see how we can help.