• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

How to create a check constraint to force one of two fields to be null

I've inherited a database that has several inconsistencies in the data.   I'm trying to work to block some of these using check constraints.  

I have a single table where all values are nullable, but the logic requires that one of two (datetime) columns (at a minimum) be null.   Both can be null, but if one has a date in it, the other cannot have a date in it.    I'm trying to figure out a way to enforce this through constraints.   I am trying to avoid using an indexed view because they're really a pain for me.  I was hoping there was a way to write a check constraint to prevent this.  

Thanks in advance.  
0
smcintire001
Asked:
smcintire001
  • 4
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Both can be null, but if one has a date in it, the other cannot have a date in it.

These kind of constraints cannot be created in Database level..
You need to validate these kind of constraints from your application and need to be done at application level.

At Database level, you can

1. Enforce Non-Nullabilility of both columns
2. Give a Default value in case of absence of any data input

Workaround:

1. Make both columns as Nullable
2. While inserting records into the table, validate this from the application before inserting..
0
 
profyaCommented:
What version your MySQL is? If 5.x then you can consider using triggers. http://dev.mysql.com/doc/refman/5.0/en/triggers.html
0
 
smcintire001Author Commented:
It's MSSQL 2000
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
profyaCommented:
In this case you can use both check constraints and triggers.
0
 
profyaCommented:
Create a check constraint with the following definition:

([date1] IS NULL AND [date2] IS NOT NULL OR [date1] IS NOT NULL AND [date2] IS NULL)
0
 
profyaCommented:
And this expression to allow both to be null:
([date1] IS NULL AND [date2] IS NULL OR [date1] IS NULL AND [date2] IS NOT NULL OR [date1] IS NOT NULL AND [date2] IS NULL)
0
 
smcintire001Author Commented:
That did the trick.   Thank you very much...  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now