CODEWITHSUNDEEP
mysqlsqldatabasecheck-constraints
Pejman
Pejman

Reputation: 2646

MySQL - creating table with not null on multiple columns together

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

Upvotes: 6

Views: 1257

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562901

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.

Upvotes: 4

Lukasz Szozda
Lukasz Szozda

Reputation: 176189

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.

Upvotes: 3

Related Questions