1 Reply Latest reply: Jul 8, 2011 1:59 PM by Ben Speckhard-Oracle RSS

    Add new column that is not nullable and historic data

    aschilling
      hi!

      I wanted to ask what's the best practice for the following scenario:
      a new column is added to a versioned table. the new column should not be nullable. a default value can not be given, instead the initial value must be set with an additional SQL script as it depends on some calculation/logic.
      also for the history data the column should be set, at least to some default value because the new column is needed for some operations on the data that still must work when going back in time.

      the first part is rather easy: we can add the column as nullable, patch all the current data and everything's fine so far.
      but what about the history? and how do we move the column to the not-nullable state?
      is it possible to patch the history data in the _LT table? not adding/removing rows, just setting the new column to some values.
      and after that we could set the column to not nullable then.

      anyone an idea on that?

      kind regards,

      Andreas Schilling
        • 1. Re: Add new column that is not nullable and historic data
          Ben Speckhard-Oracle
          Hi Andreas,

          There is no support for modifying historical data as it is read-only. Although nothing prevents _LT updates, it is not supported as all dml should be through the created view.  As a result, you can not set a column as 'not null' within beginDDL/commitDDL due to these historical records.  Would it be possible to leave the column as nullable and enforce it for any future dml within a trigger.  You could also update any existing data.  Then for any historical records, you could create a function to compute the value as needed (if this is even possible?).

          For example:
          SQL> select nvl(column_name, function_to_compute_column(<necessary parameters>)) from ...

          Obviously, this might not work if it depends on more than just the other row values. If you give me more details on how the column is computed, I might have further suggestions.

          Regards,
          Ben