Sql Conditional Not Null Constraint

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    ));

I think your first stated requirement is:

IF ( B IS NULL ) THEN ( A = 'NEW' )

Apply the implication rewrite rule:

IF ( X ) THEN ( Y )   <=>   ( NOT ( X ) OR ( Y ) )

In your case;

( NOT ( B IS NULL ) OR ( A = 'NEW' ) )

Minor rewrite to take advantage of SQL syntax:

( B IS NOT NULL OR A = 'NEW' )

Your second stated ("extend") requirement:

IF ( A = 'NEW' ) THEN ( B IS NULL )

Apply rewrite rule:

( NOT ( A = 'NEW' ) OR ( B IS NULL ) )

Minor rewrite:

( A <> 'NEW' OR B IS NULL )

Edit: as mentioned in the other answers, a CHECK is the best method, not the trigger I originally suggested. Original text follows:

As dbaseman suggests, triggers are the way to go (not so). Try something like this (untested):

CREATE OR REPLACE TRIGGER test  BEFORE UPDATE ON table1FOR EACH ROWWHEN (new.A = 'NEW' and new.B IS NOT NULL)   RAISE_APPLICATION_ERROR (     num=> -20001,     msg=> 'B must be NULL for new rows (A = NEW)');