I have table "Players" like this:
with Players as
(select 1 Code, 0 Flag1, null Newfield from Dual
union
select 2 Code, 1 Flag1, null Newfield from Dual
union
select 3 Code, 1 Flag1, 5 Newfield from Dual
)
select * from Players
/*
1 1 0
2 2 1
3 3 1 5
*/
;
I want to achieve following needs:
1. If "Players.flag1"=1 then Default value to column Newfield must be "4".
2. If "Players.flag1"<>1 then Default value to column Newfield doesn't exist, it can be even empty, anything.
3. Triggers are not allowed to use because Database Architect doesn't allow them.
How can i achieve such conditional Default value to be put to the table?
Note that this code is not correct:
ALTER TABLE Players MODIFY NewField DEFAULT 4;
Because it always sests default value to the NewField, which is not correct. We want default value only when "flag1"=1.
Seems like we are not allowed to use such grammar:
ALTER TABLE Players MODIFY NewField DEFAULT 4 when flag1=1;
or
ALTER TABLE Players MODIFY NewField DEFAULT decode(flag1, 1, 4, null);