I have a master table for color codes which stores various unique color values. It has a column Color_value on which UNIQUE constraint has been applied. However it is accepting both ‘Black’ and ‘black’ values. How can such situations be handled?
Advertisement
Answer
You can create a unique index like this:
create unique index unique_color_value on colors(lower(color_value));
That said, it would be much simpler to make your data consistent from the start, by using a constraint that allows only lower case values in the column to start with.
create table colors ( ... color_value text unique check(color_value = lower(color_value)) )