CODEWITHSUNDEEP
sqlsql-serversql-server-2008
keithwarren7
keithwarren7

Reputation: 14268

Preventing null under certain condition

Is it possible to express a conditional validation on an insert or update to a table (SQL Server 2008 R2) that says column A cannot be null if column B is equal to 12,13 or 14.

Upvotes: 2

Views: 122

Answers (3)

onedaywhen
onedaywhen

Reputation: 57093

In first-order logic, this is known as implication:

IF x THEN y

Applying the implcation law, the above can be transformed to this:

( NOT ( x ) ) OR y

Substituting your expressions:

IF column B is equal to 12,13 or 14 THEN A cannot be null

Applying the implcation law:

( NOT ( column B is equal to 12,13 or 14 ) ) OR A cannot be null

Substituting SQL expressions:

( NOT ( B IN (12, 13, 14) ) ) OR ( NOT ( A IS NULL ) )

Applying De Morgan's laws:

( NOT x ) OR ( NOT y ) is equivlent to NOT ( x AND y )

Hence:

NOT ( B IN (12, 13, 14) AND A IS NULL )

SQL DDL:

ALTER TABLE YourTable ADD 
   CONSTRAINT your_rule
      CHECK ( NOT ( B IN (12, 13, 14) AND A IS NULL ) );

Upvotes: 0

Abe Miessler
Abe Miessler

Reputation: 85126

Yes, you could do this using a Check Constraint

Something like this?

ADD CONSTRAINT check validCHECK ( 
       ((B BETWEEN 12 and 14) AND A IS NOT NULL) 
        OR B NOT BETWEEN 12 and 14 
   );

Upvotes: 1

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

You can implement this with a table level CHECK constraint:

ALTER TABLE YourTable 
ADD CONSTRAINT CK_YourCheck 
CHECK(ColumnA IS NOT NULL OR ColumnB NOT IN (12, 13, 14))

Upvotes: 7

Related Questions