Conditional constraint.

[ssv45324]

I need to make a column not null when a value in some other column is not null.
How can i acheive this in SQL?

 

[vongrunt]

with table CHECK constraint:

ALTER TABLE blah
ADD CONSTRAINT chk_anynameyoulike
CHECK (NOT (someOtherColumn IS NOT NULL AND aColumn IS NULL))

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[TheBugSlayer]

You can create a trigger on the table that updates Col2 to NULL when Col1 is NOT NULL.

 

[vongrunt]

I'd say there is no need for trigger - because integrity rule does not access data outside current row.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[TheBugSlayer]

Vongrunt, I thought both columns would be on the same row.

ssv45324, a check constraint will not update the column value for you. Is it your intention to change the value or just to ensure a column is null when another one is not?

 

[ssv45324]

My intention is not to change, just to ensure that a column in null when another one is not.