sql server - One of the column between two columns should be NOT NULL. How to enforce it in schema?

Question

I have a table with following scehma

CREATE TABLE MyTable  
(  
    ID                INTEGER DEFAULT(1,1),      
    FirstIdentifier   INTEGER NULL,    
    SecondIdentifier  INTEGER NULL,  
  --.... some other fields .....    
)   

Now when inserting a value, one of the column between FirstIdentifier and SecondIdentifier should be NOT NULL. Is there anyway of enforcing it via schema?

Answer 1

This is possible with using a CHECK constraint:

CHECK (FirstIdentifier IS NOT NULL OR SecondIdentifier IS NOT NULL)

While CHECK constraints are part of the table (and hence "schema"?), they may not fit the desired definition. The above CHECK is not mutually exclusive, but it could be altered to such.

Happy coding.