CODEWITHSUNDEEP
androiddatabasesqliteandroid-room
liuyong
liuyong

Reputation: 1057

How to migrate from table without primary key using room?

I'm trying to migrate to room, but the schema of my table was like this:

CREATE TABLE cache(key text, content text, time integer);

Entity:

@Entity(tableName = "cache")
public class Cache{
    public Integer id;
    public String key;
    public String content;
    public Integer time;
}

No primary key was declared explicit, an error would occur while build:

An entity must have at least 1 field annotated with @PrimaryKey

I tried to add primary key to the table, but seems sqlite does not support that, anyone could help me?

Upvotes: 7

Views: 5807

Answers (2)

MJM
MJM

Reputation: 5301

I have tested the code,you can add new column in your existing database using a migration rules

Migration Rule

 static final Migration MIGRATION_1_2 = new Migration(1, 2) {
        @Override
        public void migrate(android.arch.persistence.db.SupportSQLiteDatabase database) {
            database.execSQL("ALTER TABLE cache ADD COLUMN id INTEGER primary KEY AUTOINCREMENT");
        }
    };

Add MigrationRule to Room database

Room.databaseBuilder(context.getApplicationContext(), AppDatabase.class, DATABASE_NAME)
        .addMigrations(MIGRATION_1_2)
        .allowMainThreadQueries()
        .build();

New Entry class is

@Entity(tableName = "cache")
public class Cache{
    @PrimaryKey(autoGenerate = true)
    public Integer id;
    public String key;
    public String content;
    public Integer time;
}

Don't forgot Room database version is SQLite db version+1

Upvotes: 0

Dave Anders
Dave Anders

Reputation: 847

Excerpt from here: http://www.sqlitetutorial.net/sqlite-primary-key/

Unlike other database systems e.g., MySQL, PostgreSQL, etc., you cannot use the ALTER TABLE statement to add a primary key to an existing table.

To work around this, you need to:

  • Set the foreign key check off
  • Rename the table to another table name (old_table)
  • Create a new table (table) with the exact structure of the table you have been renamed
  • Copy data from the old_table to the table
  • Turn on the foreign key check on

Upvotes: 3

Related Questions