MySQL - creating table with not null on multiple columns together

Question

Is there anyway to create a table with multiple columns and 2 of them should never be null in same record.

for example, I need to make C and D somehow that each one of them could be null if the other wasn't null in same record.

I there any way?

| A | B | C | D | E |
|---|---|---|---|---|
|   |   |   |   |   |
|   |   |   |   |   |
|   |   |   |   |   |

And they should never have value together

Answer 1

MySQL doesn't support CHECK constraints, as @lad2025 mentioned. You can do this with a trigger, as @RaymondNijland commented.

Here's what it might look like (tested on MySQL 5.6.37):

mysql> DELIMITER ;;

mysql> CREATE TRIGGER not_both_null BEFORE INSERT ON a_table_with_multiple_columns 
  FOR EACH ROW BEGIN
    IF (NEW.c IS NULL AND NEW.d IS NULL) THEN
      SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'One of C and D must be non-null';
    END IF;
  END ;;

mysql> DELIMITER ;

mysql> insert into a_table_with_multiple_columns set c=null, d=null;                                                                                                                      
ERROR 1644 (45000): One of C and D must be non-null

Remember to create a similar trigger BEFORE UPDATE to check for the invalid condition, or else invalid data can sneak in via UPDATE after the row has been created.