0

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.

0
4

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 <> 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 <> 2 or PatientId is not null);
2

In the check, you can use a variety of statements, including case. For that specific example you could use:

alter table tbl 
  add constraint cons_name 
  check (case 
    when status = 2 
         and PatientId is null 
    then false
    else true end)

It would test for that specific condition and nothing else.

0

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