CODEWITHSUNDEEP
mysqlsqlcreate-table
AlikElzin-kilaka
AlikElzin-kilaka

Reputation: 36051

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

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.

Upvotes: 0

Views: 94

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562911

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.

Upvotes: 1

user330315
user330315

Reputation: 

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.

Upvotes: 1

John Bollinger
John Bollinger

Reputation: 181724

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.

Upvotes: 1

Related Questions