• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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