38

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?

Silverlight Student
  • 3,968
  • 10
  • 37
  • 53

1 Answers1

45

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.

  • Do you have know if Check constraints can be replicated (in merge replication)? – Silverlight Student Aug 10 '11 at 21:20
  • @Silverlight Student Sadly, I have not played with replication :( It should work [or not work] as any other table-level schema though. –  Aug 10 '11 at 21:20
  • no worries, i will post a new question. Thanks for your help – Silverlight Student Aug 10 '11 at 21:26
  • 5
    This enforces that at least one of the fields is not null. My interpretation of the question is that exactly one should be non null. – Gavin Wahl Feb 17 '16 at 00:47
  • 14
    A bit late to the party, but if exactly one of the columns is allowed to be NULL, the CHECK constraint could be CHECK ((FirstIdentifier IS NOT NULL OR SecondIdentifier IS NOT NULL) AND ([FirstIdentifier] IS NULL OR [SecondIdentifier ] IS NULL)) – Niklas Wulff Jan 25 '18 at 10:03
  • 1
    Is there a convention on how to display this in a database diagram? – erict Nov 30 '21 at 16:07