CODEWITHSUNDEEP
mysqldatabaseconstraints
user2790103
user2790103

Reputation: 341

MySQL Check constraints: only one column is not null

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

Upvotes: 0

Views: 1691

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

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)

Upvotes: 4

Related Questions