Building a conditional check constraint

Question

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.

Answer 1

CHECK (NOT (flag_afil = 1 AND fec_renum IS NULL))