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

Question

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?

Luke Woodward · Accepted Answer · 2018-11-13 23:16:33Z

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

Actually - ODDLY - in a CONSTRAINT, a condition is "satisfied" if it is either TRUE or UNKNOWN, so you don't need to handle NULL separately. CHECK(active = 'Y') suffices. (Although... for clarity it is probably better to spell it out explicitly anyway.)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related