sql - How to check if column has value if another column is populated in SQL Server?

Question

I am trying to find a way to see if I can write a query to check a table to return any row that is missing a dependency if that makes sense. For example let's say I have the following columns:

EffectiveDate    Change1    Change2    Change3

If EffectiveDate has a value, then either Change1, Change2, or Change3 must also have a value since EffectiveDate has a value. Also vice versa, if columns Change1, Change2, or Change3 have a value, then EffectiveDate must have a value.

I need the query to return any rows where it doesn't meet the criteria above and show the columns as NULL so that I know which records to go in to fix any missing values.

So far I've only got the below, it's not much but I can't seem to put the logic together from here. I assume I will need nested CASE statements?:

SELECT employee, 
    EffectiveDate, 
    Change1, 
    Change2, 
    Change3, 
    CASE WHEN EffectiveDate IS NOT NULL OR EffectiveDate != '' 
    THEN ...
FROM table1 

标签： sqlsql-serverdatabase

Answer 1

You seem to want a check constraint:

alter table table1 add constraint chk_effectivedate_changes
    check ( (EffectiveDate is null and Change1 is null and   Change2 is null and Change3 is null) or
            (EffectiveDate is not null and (Change1 is not null or Change2 is not null or Change3 is not null)
            )
          );