Hi
I want to create a check constraint on a column , so that the column value should always be in given list and can also contain NULL value
So I created the below check constraint as below:
create table tbl_status ( name varchar2(20) , status varchar2(50))
alter table tbl_status add constraint tbl_status_chk1 check (upper(STATUS) in ('NOT APPLICABLE','NOT STARTED','IN PROGRESS','COMPLETED',NULL));
1)insert into tbl_status values ('Check1','NOT STARTED'); --Successfully inserted
2)insert into tbl_status values ('Check2','NOT APPLICABLE'); --Successfully inserted
3)insert into tbl_status values ('Check3','COMPLETED'); --Successfully inserted
4)insert into tbl_status values ('Empty',''); --Successfully inserted
5)insert into tbl_status values ('Violated','NOTAPPLICABLE'); --Successfully inserted
6)insert into tbl_status values ('Violated','not APPLICABLE'); --Successfully inserted
As per my requirement the 5th Insert shouldn't happen , Since the value - 'NOTAPPLICABLE' (withour Space) is not in my List.
The 6th Insert shouldn't happen , Since the value - 'not APPLICABLE' ( lower case) contains lower case
Kindly advice , Thanks in advance