0

Is it safe to add non-nullable columns in new tables of the schema in oracle?
Assuming that I want to preserve old data when migrating.

I have a database and a service that reads and writes from it. In the service, I have two dao classes (mappers), one for reading the database record another for creating. When I add new columns to the database, I obviously have to adjust the mappers accordingly to read and write to/from new columns.

The question is : If I add new non-nullable columns in new tables to the schema, what will happen to old records when I try to read them. Since the read mapper was adjusted to always read the new column, it will certainly check for that column in the schema and find it missing.

How do you approach this issue, do you set a default value to new non-nullable columns?

JavaGeek
  • 335
  • 1
  • 2
  • 11
  • 1
    If it is a non-nullable column, you have to populate a default that is non-null first. – Romain Hippeau Dec 06 '21 at 17:10
  • 1
    You say "new tables" several times but I assume since you're talking about existing data and existing DAO classes that you're really talking about "existing tables". How would you add a non-nullable column to an existing table with existing data without specifying a default? Are you thinking of creating a non-validated, non-enforced constraint? – Justin Cave Dec 06 '21 at 17:12
  • I would rather say I am actually creating new tables with non-null constraints. I know that adding non-nullable columns to an existing table requires a default value, but does that also applies to new tables with non-nullable columns? – JavaGeek Dec 06 '21 at 19:25
  • 1
    No, it doesn't. When you're creating a new table, you decide which columns can (or can not) accept NULL values, and which will have (or will not have) a DEFAULT value. – Littlefoot Dec 06 '21 at 20:07
  • 1
    If you want to add a non-nullable column to a table without a default value you should first add it as nullable, then update all your data and finally add the `not null` constraint afterwards (3 step process). – Martin Schapendonk Dec 07 '21 at 18:31

0 Answers0