Two nullable columns one required to have value

Question

No-Explanation Question:

Is there anyway to have a constrain of 2 null values that always requires 1 to have value ? For example two date columns both null but having at least 1 that requires to have a value

Problem Description:

Let's say I have table called Expense

and have 2 dates :

prevision_expense_expiration_date DATE NULLABLE expense_payment_date DATE NULLABLE

the logic of those 2 columns is the following:

I made a purchase of something and I know I have to pay for it, some date, like a phone bill. I will enter this as an expense with a expense_payment_date. This date is the supposed date I should pay but not the actual date of the payment, like the expiration date of the invoice.

In other situation I sell a gift card of some provider for it's service. I may have the expense of buying to my provider the service transfered to my client only if the client redeem the card. Hence the gift card has an expiration date, I want to do a prevision for that 'expense' without inserting as an expense for the time the gift card is valid, if the gift card expires, that 'expense' should not enter into the account system.

I know I can have 2 equally tables called prevision_expense and confirmed_expense but doesn't sounds right so I have in the same table, 2 dates, nullable, but I want to constrain or something so that one is always required.

There's another posible strategy:

payment_date DATE NOT NULL is_prevision_date BOOL NOT NULL

So, in this case, if the date is prevision bool value would be 1, otherwise will be 0. No null values, all is good. except that I want the option to store BOTH values when first I have a prevision date and THEN (lets says two days later) have a confirmed date for that expense, in which case with strategy 2 i won't have that option.

Am I doing everything wrong in the database design ? :D

Answer 1

A version of JD Schmidt's answer, but without the awkwardness of an extra column:

CREATE TABLE foo (
  FieldA INT,
  FieldB INT
);

DELIMITER //
CREATE TRIGGER InsertFieldABNotNull BEFORE INSERT ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot both be null';
  END IF;
END//
CREATE TRIGGER UpdateFieldABNotNull BEFORE UPDATE ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot both be null';
  END IF;
END//
DELIMITER ;

INSERT INTO foo (FieldA, FieldB) VALUES (NULL, 10); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (10, NULL); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (NULL, NULL); -- gives error
UPDATE foo SET FieldA = NULL; -- gives error

Answer 2

If you are using SQL Server, you can avoid the use of a trigger by using a persisted computed column in your table:

CREATE TABLE Test_Constraint
(
    A DateTime Null,
    B DateTime Null,
    A_and_B AS (CASE WHEN A IS Null AND B IS Null THEN Null ELSE Convert(Binary(1), 1) END) PERSISTED Not Null 
);

The case statement in the computed column A_and_B will return a null value if both column A and B are null, but the Not Null constraint on the computed column would then raise an error preventing the insert. Otherwise it returns a 1.

Since the computed column is persisted, it will be physically stored in the table. The convert to binary minimizes the impact of this, making the column a binary datatype of length 1.

Answer 3

I found an article that looks like the same thing here

CREATE TABLE foo (
  FieldA INT,
  FieldB INT,
  FieldA_or_FieldB TINYINT NOT NULL;
);

DELIMITER //
CREATE TRIGGER FieldABNotNull BEFORE INSERT ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SET NEW.FieldA_or_FieldB = NULL;
  ELSE
    SET NEW.FieldA_or_FieldB = 1;
  END IF;
END//
DELIMITER ;

INSERT INTO foo (FieldA, FieldB) VALUES (NULL, 10); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (10, NULL); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (NULL, NULL); -- gives error