Search code examples
androidkotlinandroid-room

Sqliteconstraintexception unique constraint failed on migration. Room

I want to populate a column in a database with a new unique string value generated by UUID.randomUUID() When migrating I get a unique constraint failed codes.id error I understand that '${UUID.randomUUID()}' in the query produces the same value for all rows, but I don't see how this can be fixed. Help me please. My migration code:

class Migration_3_4 : Migration(3, 4) {

override fun migrate(database: SupportSQLiteDatabase) {
    database.execSQL("DROP TABLE IF EXISTS $MIGRATION_TABLE_CODES_OLD;")
    database.execSQL("ALTER TABLE $MIGRATION_TABLE_CODES RENAME TO $MIGRATION_TABLE_CODES_OLD ")
    database.execSQL(
        "CREATE TABLE IF NOT EXISTS $MIGRATION_TABLE_CODES " +
                "(id TEXT NOT NULL PRIMARY KEY, text TEXT NOT NULL, format INTEGER NOT NULL, " +
                "type INTEGER NOT NULL, date INTEGER NOT NULL, note TEXT NOT NULL, " +
                "isFavorite INTEGER NOT NULL)"
    )
    val sql = "INSERT INTO $MIGRATION_TABLE_CODES (id, text, format, type, date, note, isFavorite) " +
            "SELECT '${UUID.randomUUID()}', text, format, type, date, note, isFavorite " +
            "FROM $MIGRATION_TABLE_CODES_OLD"
    database.execSQL(sql)
    database.execSQL("DROP TABLE IF EXISTS $MIGRATION_TABLE_CODES_OLD")
}

companion object {
    const val MIGRATION_TABLE_CODES_OLD = "codes_old"
    const val MIGRATION_TABLE_CODES = "codes"
}

}

Solution

  • IF you wanted a probably unique text value then you could use:-

    SELECT hex(randomblob(16)),text,format,type,date,note,isfavorite

    the result would be along the lines of:-

    enter image description here

    • obviously the other columns are just static/fixed values and would in your case be the actual values
    • as far as uniqueness is concerned the above has been tested a number of times with 10000 rows.

    You could, again ONLY IF YOU want unique text values, as opposed to generated UUID's, use

    SELECT hex(random()),text,format,type,date,note,isfavorite

    The following may be more suitable as it generates UUID like values:-

    SELECT hex(randomblob(4))||'-'||hex(randomblob(2))||'-'||hex(randomblob(2))||'-'||hex(randomblob(2))||'-'||hex(randomblob(6)),text,format,type,date,note,isfavorite

    e.g. :-

    enter image description here

    • note the testing was increased to 100000 rows.

    The testing itself was undertaken using an SQLite tool (Navicat but there are other tools). The following is the SQL used for the testing:-

    /* Just in case the environmet is dirty */
DROP TABLE IF EXISTS migold;
DROP TABLE IF EXISTS mig;
DROP TABLE IF EXISTS mig2;
DROP TABLE IF EXISTS mig3;
/* Create and populate the table as if before migration */
CREATE TABLE IF NOT EXISTS mig (id INTEGER NOT NULL PRIMARY KEY, text TEXT NOT NULL, format INTEGER NOT NULL,type INTEGER NOT NULL, date INTEGER NOT NULL, note TEXT NOT NULL,isFavorite INTEGER NOT NULL);
/* insert rows into the pre-migration table*/
WITH cte(x) AS (
    SELECT 1 UNION ALL SELECT x+1 FROM cte LIMIT 100000
) 
INSERT INTO mig (text,format,type,date,note,isfavorite)  SELECT 'textval',1,2,3,'noteval',4 FROM cte
;
/* show the rows of the pre-migration table */
SELECT * FROM mig;

/* THE MIGRATION */
DROP TABLE IF EXISTS  migold;
ALTER TABLE mig RENAME TO migold;
CREATE TABLE IF NOT EXISTS mig (id TEXT NOT NULL PRIMARY KEY, text TEXT NOT NULL, format INTEGER NOT NULL,type INTEGER NOT NULL, date INTEGER NOT NULL, note TEXT NOT NULL,isFavorite INTEGER NOT NULL);
/* Some extra tables for alternative methods of generating the unique text id */
CREATE TABLE IF NOT EXISTS mig2 (id TEXT NOT NULL PRIMARY KEY, text TEXT NOT NULL, format INTEGER NOT NULL,type INTEGER NOT NULL, date INTEGER NOT NULL, note TEXT NOT NULL,isFavorite INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS mig3 (id TEXT NOT NULL PRIMARY KEY, text TEXT NOT NULL, format INTEGER NOT NULL,type INTEGER NOT NULL, date INTEGER NOT NULL, note TEXT NOT NULL,isFavorite INTEGER NOT NULL);
/* Example 1 */
INSERT INTO mig SELECT hex(randomblob(16)),text,format,type,date,note,isfavorite FROM migold;
SELECT * FROM mig;
/* Example 2 */
INSERT INTO mig2 SELECT hex(random()),text,format,type,date,note,isfavorite FROM migold;
SELECT * FROM mig2;
/* Example 3 */
INSERT INTO mig3 SELECT hex(randomblob(4))||'-'||hex(randomblob(2))||'-'||hex(randomblob(2))||'-'||hex(randomblob(2))||'-'||hex(randomblob(6)),text,format,type,date,note,isfavorite FROM migold;
SELECT * FROM mig3;
/* Cleanup test environment */
DROP TABLE IF EXISTS migold;
DROP TABLE IF EXISTS mig;
DROP TABLE IF EXISTS mig2;
DROP TABLE IF EXISTS mig3;