How to define a table in SQL with 2 fields being not-null (mandatory) dependent?

Question

Not-null dependent means that at least 1 field is not null.

Ideas?

Example: email and phone-number can be not-null dependent. If email is null, phone-number must be not-null. If phone-number is null, email must be not-null.

Someone added a tsql tag and you mention mysql in comments. What is the correct tag? — philipxy, Aug 28 '14 at 17:15

score 1 · Answer 1 · answered Aug 28 '14 at 14:53

1

CREATE TABLE T (
  EMAIL VARCHAR(256),
  PHONE VARCHAR(10)
);

ALTER TABLE T ADD CONSTRAINT
  CHECK (EMAIL IS NOT NULL) OR (PHONE IS NOT NULL);

Some SQL dialects allow or require you to put the CHECK constraint in the CREATE TABLE statement.

answered Aug 28 '14 at 14:53

John Bollinger

160,171
8
81
157

Tested on mysql. Had to change to: `CHECK ((EMAIL IS NOT NULL) OR (PHONE IS NOT NULL));` - added parenthesis. – AlikElzin-kilaka Aug 28 '14 at 15:23
On mysql, it doesn't work. The statement passed OK but had no affect. It wasn't registered as a constraint and 2 nulls in the same row could be added. – AlikElzin-kilaka Aug 28 '14 at 15:24

score 1 · Answer 2 · answered Aug 28 '14 at 14:53

1

alter table the_table add constraint check_two_columns 
     check (   (email is null and phone_number is not null) 
            or (email is not null and phone_number is null));

You didn't state your DBMS, but this will not work with MySQL however.

answered Aug 28 '14 at 14:53

Indeed doesn't work with mysql. I thought such a question was generic enough for SQL :( Any idea how to do it in mysql? – AlikElzin-kilaka Aug 28 '14 at 15:27
@AlikElzin-kilaka: the only (ugly) way to do this in MySQL is to write a trigger (or upgrade to Postgres) – Aug 28 '14 at 15:53
The strange thing is that the command didn't fail. It just didn't do anything - no constraint was created. – AlikElzin-kilaka Aug 28 '14 at 18:10
@AlikElzin-kilaka: yes, that's the MySQL way. Rather than throwing an exception it silently ignores things (or silently truncates data) – Aug 28 '14 at 18:23

score 1 · Accepted Answer · answered Aug 28 '14 at 18:32

1

MySQL doesn't support CHECK constraints, despite this feature having been requested since 2004.

Annoyingly, it parses CHECK constraint syntax, but it doesn't store the constraint or enforce it later. I have thought for a long time that this is misleading to accept an SQL statement that the RDBMS has no support for, without reporting even a warning.

To solve your problem, you could use a trigger:

CREATE TRIGGER t BEFORE INSERT ON mytable
FOR EACH ROW BEGIN
    IF COALESCE(NEW.email, NEW.phone_number) IS NULL THEN
        SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = 'Either email or phone_number must be non-null';
    END IF;
END

You also need to define a similar trigger before UPDATE.

answered Aug 28 '14 at 18:32

Bill Karwin

538,548
86
673
828

Got the error: `Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5` – AlikElzin-kilaka Aug 31 '14 at 12:53
1

@AlikElzin-kilaka, I did test this trigger before posting it. I suspect you haven't set the DELIMITER correctly, and the SQL parser thinks that the first semicolon terminates the whole CREATE TRIGGER statement. See my answer to http://stackoverflow.com/questions/745538/create-function-through-mysqldb/745575#745575 – Bill Karwin Aug 31 '14 at 16:37
1

Found a nice answer that visualizes the delimiter thingy: http://stackoverflow.com/a/10084557/435605 – AlikElzin-kilaka Sep 01 '14 at 10:25

How to define a table in SQL with 2 fields being not-null (mandatory) dependent?

3 Answers3