Is it possible to alter an existing column to accept a default value based on another column value?
For Eg:
Create table #Temp ( TempId int not null ,TransCode int --values of 1 / 2 or 3 ,ReasonCode int ) The default value for ReasonCode is based off TransCode. When TransCode is 1 and ReasonCode is Null then 99 else ReasonCode
How do I add this default constraint?
You can do it via CHECK constraint,
First you will have to create table,
create table myTemp1(TempId int not null ,TransCode int,ReasonCode int); and then add the constraint as
create table myTemp1(TempId int not null ,TransCode int,ReasonCode int);
alter table myTemp1 add constraint check_role CHECK(case when (TransCode = 1 AND ReasonCode = NULL) then 99 else ReasonCode end = ReasonCode); OR like
alter table myTemp1 add constraint check_role CHECK(ReasonCode = (case when (TransCode = 1 AND ReasonCode = NULL) then 99 else ReasonCode end = 1)) demo at http://sqlfiddle.com/#!3/d633a/1
But a Check Constraint will just check your entries and not put a default value. This is my exact same problem. However, I researched that a Calculated Column will do the trick.
