Alex Rivera | Logout

Either OR non-null constraints in MySQL

Asked 2008-10-04T15:14:28.653
12

What's the best way to create a non-NULL constraint in MySQL such that fieldA and fieldB can't both be NULL. I don't care if either one is NULL by itself, just as long as the other field has a non-NULL value. And if they both have non-NULL values, then it's even better.

sql mysql null
Edit
Report

2 Answers

8

This isn't an answer directly to your question, but some additional information.

When dealing with multiple columns and checking if all are null or one is not null, I typically use COALESCE() - it's brief, readable and easily maintainable if the list grows:

COALESCE(a, b, c, d) IS NULL -- True if all are NULL

COALESCE(a, b, c, d) IS NOT NULL -- True if any one is not null

This can be used in your trigger.

answered 2008-10-05T00:12:41.577
5

This is the standard syntax for such a constraint, but MySQL blissfully ignores the constraint afterwards

ALTER TABLE `generic` 
ADD CONSTRAINT myConstraint 
CHECK (
  `FieldA` IS NOT NULL OR 
  `FieldB` IS NOT NULL
)
answered 2008-10-04T15:38:51.880

Your Answer