CODEWITHSUNDEEP
oracle-databaseoracle11g
VictorGram
VictorGram

Reputation: 2661

How to create an unique constraint that won't allow any other character?

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,

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?

Upvotes: 1

Views: 38

Answers (1)

Luke Woodward
Luke Woodward

Reputation: 64959

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

Upvotes: 2

Related Questions