Myujth

I am using Oracle 12c. I have a table named myTestTab and that table has a column named "active". The column is defined as

ACTIVE varchar2(1)

So that only one character can be entered in that column. Now, for this column,

• There can be no more than one row that can have value 'Y' in it.


• Additionally, have to add a check constraint so that nothing but a 'Y' can be entered into that column (to prevent the user from entering any other value).

So far, to match the requirement I have created an index on that table as:

create unique index only_one_yes on myTestTab (case when upper(ACTIVE)='Y' then 'Y' else '' end);

However, it's not setting lower-case 'y' as upper-case 'y' and it is accepting any other character as long as it is unique. May I get any idea on how to fix it?

it's not setting lower-case 'y' as upper-case 'y'...

You haven't told it to. Your unique index permits at most one 'y' or 'Y' value, but indexes don't change the data going into a table. For that you will need to use a trigger, such as the following:

CREATE OR REPLACE TRIGGER biu_myTestTab_active_uc
 BEFORE INSERT OR UPDATE ON myTestTab
 FOR EACH ROW
BEGIN
 :new.active := UPPER(:new.active);
END;
/

... it is accepting any other character as long as it is unique

Actually it's accepting any other character regardless of whether it is unique. You could insert 'X' five times if you wanted to and the index wouldn't stop you. Again, that's not something the index you have defined can do. As mentioned in your question, what you need is a check constraint:

ALTER TABLE myTestTab ADD CONSTRAINT active_yes_or_null CHECK (active = 'Y' OR active IS NULL);