Sql Conditional Not Null Constraint

Question

I am curious to know is it possible to create a conditional not null constraint in sql? In otherwords is it possible to create a constraint such that a column B can be null as long column A contains lets say 'NEW' but if the contents of column A changes to something else then column B is no longer allowed to be null?
And to extend on that, it is then possible to make it so that column B must be null or empty as long as column A says 'NEW'?
Thanks All :D

Answer 1

This is perfectly fine for CONSTRAINT CHECK. Just do this:

Requirement:

is it possible to create a constraint such that a column B can be null as long column A contains lets say 'NEW' but if the contents of column A changes to something else then column B is no longer allowed to be null?

Note the phrase: column B can be null

Solution:

create table tbl (     A varchar(10) not null,     B varchar(10),      constraint uk_tbl check     (       A = 'NEW' -- B can be null or not null: no need to add AND here       OR (A <> 'NEW' AND B IS NOT NULL)     ) ); 

You can simplify it further:

create table tbl (     A varchar(10) not null,     B varchar(10),      constraint uk_tbl check     (       A = 'NEW'        OR B IS NOT NULL     ) ); 

---

Requirement mutually incompatible to requirement above:

And to extend on that, it is then possible to make it so that column B must be null or empty as long as column A says 'NEW'?

Note the phrase: column B must be null

create table tbl (     A varchar(10) not null,     B varchar(10),      constraint uk_tbl check     (       (A = 'NEW' AND B IS NULL)       OR A <> 'NEW'     ) ); 

Could be simplified with this, simpler but might not be as readable as above though:

create table tbl (     A varchar(10) not null,     B varchar(10),      constraint uk_tbl check     (       A <> 'NEW'       OR B IS NULL     ) );