Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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//

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
share|improve this answer
    
thanks, im using MySQL and this works perfectly fine with it. specially because of throwing a null value on not null column error. –  Bart May 29 '13 at 18:32
add comment

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.

share|improve this answer
1  
In SQL-Server, you can do this with a CHECK constraint, too. No need for persisted column. –  ypercube May 29 '13 at 13:40
1  
Awesome, that does seem a bit cleaner. CREATE TABLE Test_Constraint2 ( A DateTime Null, B DateTime Null, CONSTRAINT A_or_B_Not_Null CHECK (CASE WHEN A IS Null AND B IS Null THEN 0 ELSE 1 END = 1) ) –  Shane Estelle May 29 '13 at 15:28
    
great answer! This one is more appropiate than the other but since I'm using MySQL, CHECK CLAUSE is parsed but ignored on MySQL so I mark the other answer as the accepted one. +1 –  Bart May 29 '13 at 18:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.