Oracle Database Discussions

in One of the table column, you want to add check constraint and want to accept only ''Yés'' and ''No'' as acceptable value and the column is nullable and If you by mistake add NULL keyword in check constraint which is accepted and will not provide any syntactical error, all will be messed, Check constraint became elephant's teeth, only for display purpose no usage.

Test scenario and results:

Create table T_Check_constraint (

col1 varchar2(50),

chk_cons_col varchar2(50));

Alter table T_Check_constraint add constraint check_null check (chk_cons_col in (NULL, 'Yes', 'No'));

insert into T_Check_constraint (col1, chk_cons_col) values ('row1', 'Yes');

insert into T_Check_constraint (col1, chk_cons_col) values ('row2', 'No');

insert into T_Check_constraint (col1) values ('row3' );

insert into T_Check_constraint (col1, chk_cons_col) values ('row4', 'Violating check costraint');

select * from T_Check_constraint;

Hmmmm... indeed a poor implementation, violating the purpose of check constraint completly, looks oracle should have handle it in a far better way.

In case of Check Constraint oracle considering null comparison with any value as TRUE, that causing the issue, Oracle may have restricted use of null in check constraint. Is not it a logical flaw?