Modify existing column with DEFAULT value

When you modify existing column with DEFAULT value, then modification to add a DEFAULT value takes effect only from subsequent insertions to the table.

Let’s have a look at EMP table below. All DEFAULT values are set to NULL at this point

Let’s modify EMP .COMMISION_PCT   DEFAULT value to 0.777

Now, the EMP .COMMISION_PCT   DEFAULT value was set to 0.777, see below

Let insert one row into EMP table to verify DEFAULT value triggered

And verify

As we can see, a new row was inserted with  DEFAULT value of 0.777 (was rounded to 0.78)


ATTENTION: Keep in mind that adding a new column with DEFAULT value to a table, will act differently. It will store DEFAULT value in the dictionary while inserting DEFAULT value at every NULL field. Modifying existing column DEFAULT value won’t trigger immediate changes to NULL values in this column, instead, DEFAULT values will be used ONLY from subsequent insertions to the table. This difference in behavior worth to be remembered.


Leave a comment

Your email address will not be published. Required fields are marked *