CODEWITHSUNDEEP
androidsqlsqliteandroid-room
Sabre
Sabre

Reputation: 4183

NOT NULL constraint failed while copying data to a new table

I migrate my database from SQLiteOpenHelper to Room.

I have a table that I want to change, lets call it "my_table". Its simplified create statement:

CREATE TABLE `my_table`
 (`_id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `title` TEXT
 )

During an upgrade among other changes I add the new column type INTEGER NOT NULL (I'm adding Foreign Key aswell and doing other significant changes, that's the reason to create a new table instead of altering the existing one):

CREATE TABLE "new_table" 
 (`_id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `title` TEXT,
  `type` INTEGER NOT NULL
 )

Then I want to copy data from the my_table to the new_table and set type column's values.

SQL statement:

INSERT INTO new_table (title) 
SELECT title FROM my_table;
UPDATE new_table SET type = 1;
DROP TABLE my_table;
ALTER TABLE new_table RENAME TO my_table;

Android migration:

public static final Migration MIGRATION_TEST = new Migration(1, 2) {
    @Override
    public void migrate(@NonNull SupportSQLiteDatabase database) {
        // Create new table
        database.execSQL("CREATE TABLE new_table (`_id` INTEGER PRIMARY KEY AUTOINCREMENT, `title` TEXT, `type` INTEGER NOT NULL)");
        // Copy some data
        database.execSQL("INSERT INTO new_table (title) SELECT title FROM old_table"); // constraint violation
        // Insert default value into the measures column
        database.execSQL("UPDATE new_table SET type = 1");
        // Delete old table
        database.execSQL("DROP TABLE old_table");
        // Rename new table
        database.execSQL("ALTER TABLE new_table RENAME TO my_table");
    }
};

Obviously I get NOT NULL constraint failed: new_table.type error:

android.database.sqlite.SQLiteConstraintException: NOT NULL constraint failed: new_table.type (code 1299)
Error Code : 1299 (SQLITE_CONSTRAINT_NOTNULL)
Caused By : Abort due to constraint violation.
(NOT NULL constraint failed: new_table.type (code 1299))

I can avoid it by changing new table's create statement and setting default value for the type column.

CREATE TABLE "new_table" 
 (`_id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `title` TEXT,
  `type` INTEGER NOT NULL DEFAULT 1
 )

But I don't want to do this as Room doesn't suport default values out of the box and in order to avoid future mistakes when inserting new values into tables.

Are there any workarounds to avoid this error while inserting data to a new table?

Upvotes: 2

Views: 1340

Answers (1)

MikeT
MikeT

Reputation: 57043

I think the following may work :-

database.execSQL("INSERT INTO new_table (title,type) SELECT title, 1 FROM old_table");

That is, you are now saying to INSERT 2 columns as per the SELECT statement. The SELECT returns 2 values the title from the old_table and the literal value 1.

You then wouldn't need database.execSQL("UPDATE new_table SET type = 1").

Upvotes: 3

Related Questions