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.
