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.