Set a check constraint for a set of conditions which aren't universally true I need to make sure my database is self-consistent. There are two columns, which share a relationship when there is some specific value the other should be not null. I tried using constraint check (status = 2 and PatientId is not null), but it returns 

SQL Error [4025] [23000]: (conn=20) CONSTRAINT `pId_status` failed for `prod`.`sch`


since I need an specific check for every value of status and some of them aren't neither for null or the PatientId column. I'm using mariadb and I'm aware that checks are not ignored as mysql.

Other status values may or may not accept patientid to be null, so this check should first make sure that status is 2 and then if patientid is not null. Status is not nullable. The above is just the simplest check that should conform business logic, since that status always have a patientid.
Since the status column is not nullable, the check constraint expression in your own solution

case
  when status = 2 and PatientId is null then false
  else true
end


is equivalent to this more concise expression:

not (status = 2 and PatientId is null)


Applying De Morgan's laws, you can transform it further:

        not (status = 2 and PatientId is null)   →

    →   not (status = 2) or not (PatientId is null)   →

    →   status &lt;> 2 or PatientId is not null


which is what McNets suggested in the comments.

Using the final expression, your check constraint definition would look like this:

alter table tbl 
  add constraint cons_name 
  check (status &lt;&gt; 2 or PatientId is not null);