2

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.

6
CHECK (NOT (flag_afil = 1 AND fec_renum IS NULL)) 
  • Equivalently, CHECK (flag_afil != 1 OR fec_renum IS NOT NULL)) (from De Morgan's Laws), or since the flag can only have one of two values, CHECK (flag_afil = 0 OR fec_renum IS NOT NULL)). I find the last to be the most intuitive. – jpmc26 Apr 10 at 23:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.