0

This question is similar to Check Constraint to Confirm Exactly One is not NULL, but for MySQL and multiple columns, and MySQL supports check since v8.0.16.

For example, I have a "Post" Table and it has three columns which are author_id, manager_id, editor_id. Only one of these three columns should be not null.

How to achieve this in MySQL Check constraints?

Maybe something like

not_null_sum = (author_id is not null ? 1 : 0) + (manager_id is not null ? 1 : 0) + (editor_id is not null ? 1 : 0)
not_null_sum should == 1
user2790103
  • 315
  • 3
  • 11

1 Answers1

4

Try summing up a boolean expression which checks the total count of null columns:

CHECK (author_id IS NULL + manager_id IS NULL + editor_id IS NULL = 1)
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 3
    Just test it. `CHECK ((author_id IS NOT NULL) + (manager_id IS NOT NULL) + (editor_id IS NOT NULL) = 1)` this works well. Thanks! – user2790103 Jun 03 '20 at 04:50