How to add a unique index on an existing table column?

[pedrommcarrasco]

Hey 👋

I previously added this migration:

migrator.registerMigration("5") { db in
    try db.create(table: "badges") { t in
        t.primaryKey("id", .text).notNull().unique()
        t.column("tier", .text).notNull()
        t.column("name", .text).notNull()
        t.column("key", .text).notNull()
        t.column("hint", .text).notNull()
        t.column("imageUrl", .text).notNull()
    }
}

However, I recently discovered a bug: the key column should have been marked as .unique(), but it isn’t.

Is there a clean way to migrate this table now to enforce uniqueness on the key column and have it properly reflected in the schema?

[groue]

Hello @pedrommcarrasco,

Is there a clean way to migrate this table now to enforce uniqueness on the key column and have it properly reflected in the schema?

Please follow precisely the instructions at How to Recreate a Database Table.

You have to be careful because the badges table may contain duplicate entries for a given key, due to the lack of unique index. You MUST come up with a strategy for disambiguating those entries, here, so that you have the guarantee that the fix runs correctly on all user devices.

In the following sample code, I'll prefer the most "recent" row, by picking the row with the maximum rowid (a value that all SQLite tables have, and is usually growing). You will have to adapt this sample code if you want to use another strategy:

migrator.registerMigration("Add unique index on badges.key") { db in
    try db.create(table: "new_badges") { t in
        t.primaryKey("id", .text).notNull() // <- already unique
        t.column("tier", .text).notNull()
        t.column("name", .text).notNull()
        t.column("key", .text).notNull().unique() // <- add the missing index
        t.column("hint", .text).notNull()
        t.column("imageUrl", .text).notNull()
    }
    try db.execute(sql: """
       INSERT OR IGNORE -- ignore duplicates on the 'key' column
       INTO new_badges
       SELECT * FROM badges
       ORDER BY rowid DESC -- tie breaker
       """)
    try db.drop(table: "badges")
    try db.rename(table: "new_badges", to: "badges")
}

Note: I did not add unique() on the primary key, because primary keys are already unique by definition.

[pedrommcarrasco]

Appreciate it the quick reply, thank you!

[groue]

Happy GRDB!

[pedrommcarrasco]

Always!