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

Question is a bit wierd.. But you can can handle this user case with a trigger. — Raymond Nijland, Apr 18 '18 at 14:43
I removed the php and mysqli tags, because the question does not pertain to PHP. — Bill Karwin, Apr 18 '18 at 18:46

Bill Karwin · Accepted Answer · 2018-04-18T20:26:46.030

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.

Lukasz Szozda · Answer 2 · 2018-04-18T14:50:06.417

You could achieve it using CHECK constraint:

CREATE TABLE tab(i INT PRIMARY KEY,
                 a INT,
                 b INT,
                 c INT,
                 d INT 
                 CHECK (NOT(c IS NULL AND d IS NULL))
                 );

DBFiddle Demo-MariaDB

INSERT INTO tab(i,a,b,c,d) 
VALUES(1,1,1,NULL,NULL);
-- error: CONSTRAINT `d` failed for `tab`

INSERT INTO tab(i,a,b,c,d) 
VALUES(2,1,1,1,NULL);
-- ok

INSERT INTO tab(i,a,b,c,d) 
VALUES(3,1,1,NULL,1);
-- ok

INSERT INTO tab(i,a,b,c,d) 
VALUES(4,1,1,1,1);
-- ok

Unfortunately in MySQL create-table:

The CHECK clause is parsed but ignored by all storage engines.

MySQL - creating table with not null on multiple columns together

2 Answers2