How to add migration if we changed column type TEXT NOT NULL to TEXT in Room DB in android

Question

I have a table in which I have to add two columns Class and school and I have added it successfully by using migration as

  static final Migration MIGRATION_2_3 = new Migration(2, 3) {
        @Override
        public void migrate(SupportSQLiteDatabase database) {

            database.execSQL("ALTER TABLE LearningMaterialChildDetailEntity " + " ADD COLUMN classes TEXT NOT NULL");
            database.execSQL("ALTER TABLE LearningMaterialChildDetailEntity " + " ADD COLUMN school TEXT NOT NULL");

        }
    };

But it gives me exception as

Caused by android.database.sqlite.SQLiteException
Cannot add a NOT NULL column with default value NULL (code 1 SQLITE_ERROR): , while compiling: ALTER TABLE LearningMaterialChildDetailEntity ADD COLUMN classes TEXT NOT NULL

What should I do for solving that exception, either I have to make TEXT NOT NULL to TEXT if yes then how, Please guide me.

Answer 1

If you ADD a column and it has the NOT NULL constraint then you must supply a DEFAULT value, so that any existing rows can be set to a value which cannot be the implicit NULL.

As per

If a NOT NULL constraint is specified, then the column must have a default value other than NULL.

• https://www.sqlite.org/lang_altertable.html

So you will have to use something along the lines of :-

ALTER TABLE LearningMaterialChildDetailEntity ADD COLUMN classes TEXT NOT NULL DEFAULT '';