Skip to Main Content
  • Questions
  • Column default value as another column from same table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, PRIYANK.

Asked: June 15, 2016 - 1:38 am UTC

Last updated: May 10, 2021 - 7:52 am UTC

Version: Oracle 11g 11.0.2.4

Viewed 10K+ times! This question is

You Asked

Hello,

We have a requirement to add a new column in the table which needs a default value like column1 || column2.
For some reason application code can not be changed to deal with this new column and so the default value.

I thought of two approaches that can solve this, using trigger to update the new column if any of two columns column1 or column2 are updated - so the new column can be initially updated and then trigger can be enabled to handle any future changes.
Other approach is use virtual column.

Now it seems that a direct insertion of data or an update might be required for the new column, that rules out the virtual column.
And on trigger, web is full of articles that they are problematic and I am having tough time convincing that for a low volume table (number of records as well as the number of transactions) trigger may not be the worst idea, though I understand the maintenance headaches and side effects etc.

Is there any other approach?
Also why Oracle does not support the column default value as another column?

Thank you,
Priyank

and we said...

Perhaps a variation like this would be sufficient ?


SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t ( c1 int ,c2 int );

Table created.

SQL> alter table t add c_override int;

Table altered.

SQL> alter table t add c3 int generated always as ( nvl(c_override,c1+c2));

Table altered.

SQL>
SQL> insert into t (c1,c2) values (1,2);

1 row created.

SQL> insert into t (c1,c2,c_override) values (3,4,10);

1 row created.

SQL> select * from t;

        C1         C2 C_OVERRIDE         C3
---------- ---------- ---------- ----------
         1          2                     3
         3          4         10         10

2 rows selected.

--
-- you can hide c_override as well if you like
--

SQL>
SQL> alter table t modify c_override invisible;

Table altered.

SQL> insert into t (c1,c2,c_override) values (5,6,20);

1 row created.

SQL>
SQL> select * from t;

        C1         C2         C3
---------- ---------- ----------
         1          2          3
         3          4         10
         5          6         20

3 rows selected.

--
-- but you still need the app to know its a virtual column not a real one
--
SQL> insert into t (c1,c2,c3) values (1,2,3);
insert into t (c1,c2,c3) values (1,2,3)
                     *
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
SQL>
SQL>


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

?

Ghassan, June 16, 2016 - 5:54 am UTC

Invisible cols aren't from 12c?
Connor McDonald
June 16, 2016 - 6:49 am UTC

True. The same can be achieved with a view in earlier versions.

Thank you!

PRIYANK PANDEY, June 23, 2016 - 2:18 pm UTC

Thank you Connor!
This was elegant and worked out very well.

Regards,
Priyank
Chris Saxon
June 24, 2016 - 2:12 am UTC

Glad we could help

Thank you!!

Shivam Thacker, May 08, 2021 - 1:27 pm UTC

This is very helpful.
I have a question.

How can we update the value of c_override?
Connor McDonald
May 10, 2021 - 7:52 am UTC

Just with an update statement - its just a normal column

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library