Building a conditional check constraint I'm trying to build a conditional check constraint for my table TBL_AFIL, which has two fields:


Fec_Renun DATE
flg_afil INTEGER


Constraint: IF flg_afil=1 THEN Fec_Renun can not be NULL.

ALTER TABLE TBL_AFIL
ADD CONSTRAINT FecRenun_Not_Null CHECK (Fec_Renun IS not NULL and flg_afil =1) ;


But it throws this error:

ORA-02293: cannot validate (FecRenun_Not_Null) - check constraint violated


But when I run this query, it returns 0 rows.

SELECT * FROM TBL_AFIL
WHERE FEC_RENUN IS NULL
AND FLG_AFIL =1;


I know that to this constraint I could add ENABLE NOVALIDATE, but I'm intrigued why can't build this constraint.
CHECK (NOT (flag_afil = 1 AND fec_renum IS NULL))