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  : (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.