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

Posted on 2009-12-16
Last Modified: 2012-08-14
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.  
Question by:smcintire001
    LVL 57

    Expert Comment

    by:Raja Jegan R
    >> 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


    1. Make both columns as Nullable
    2. While inserting records into the table, validate this from the application before inserting..
    LVL 14

    Expert Comment

    What version your MySQL is? If 5.x then you can consider using triggers.

    Author Comment

    It's MSSQL 2000
    LVL 14

    Expert Comment

    In this case you can use both check constraints and triggers.
    LVL 14

    Expert Comment

    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)
    LVL 14

    Accepted Solution

    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)

    Author Closing Comment

    That did the trick.   Thank you very much...  

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now