Oracle Unique Constraint - Mixed Case

Question

I have an Oracle table that has the following columns:

ID
FUNCTION_ID
FUNCTION_ROLE

The ID column is unique (sequence) but I also want both (FUNCTION_ID, FUNCTION_ROLE) combination to also be unique.

My query is though, as the FUNCTION_ROLE is a string and the value can be mixed case, what is the best approach to prevent this from happening, i.e.:

Data for this table:
Row 1: 1,1,TEST1
Row 2: 2,1,Test1

I would expect to get a unique constraint violation when trying to insert Row 2 as ‘Test1’ is the same as ‘TEST1’ – just the case is different.

How can I prevent this as one approach I was thinking was creating another column called FUNCTION_ROLE_UPPER and using this column along with FUNCTION_ID to check for uniqueness – would this be the correct approach?

Answer 1

You can achieve this using function-based index:

CREATE UNIQUE INDEX YOUR_TABLE_U1 ON YOUR_TABLE(FUNCTION_ID, UPPER(FUNCTION_ROLE))

Under Oracle 11, you can also consider putting UPPER(FUNCTION_ROLE) in a virtual column.