CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012constraints
EvilDr
EvilDr

Reputation: 9580

Revised: SQL constraint to enforce column2 value based on column1

I need to create a table check constraint to achieve the following:

  1. If column1 is null, then column2 must be null
  2. If column1 is not null, then column2 can be either null or not null

I've written this constraint, but just wondered if you pro's could cast your expert eyes on it. I have achieve this correctly, or in the best way? 

ALTER TABLE MyTable ADD CONSTRAINT [const_name] CHECK
(
(Column1 IS NULL AND Column2 IS NULL) OR 
(Column1 IS NOT NULL AND (Column2 IS NOT NULL OR Column2 IS NULL))
)

Upvotes: 0

Views: 151

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

Simpler form, based on your new rules:

ALTER TABLE MyTable ADD CONSTRAINT [const_name] CHECK
(
    (Column2 IS NULL OR Column1 IS NOT NULL)
)

Think about it - if Column2 is null then we don't need to assert anything about Column1 - it can be NULL or not and you're happy.

Similarly, if Column1 is not NULL then we don't care whether Column2 is NULL or not.

The only case where we want this constraint to fail is specifically when Column2 isn't NULL but Column1 is - exactly the false case for the above OR condition.

Upvotes: 2

Related Questions