How to alter a table and add onDelete cascade constraint sqlite

user_909317 0
Viewed 699

I am using room database I've a table in which I forgot to add onDelete = CASCADE

Previous version

@Entity(indices = {@Index(value = {"info_id"})}, foreignKeys = @ForeignKey(entity = StudentClass.class, parentColumns = "id", childColumns = "info_id"))

What I want now

@Entity(indices = {@Index(value = {"info_id"})}, foreignKeys = @ForeignKey(entity = StudentClass.class, parentColumns = "id", childColumns = "info_id", onDelete = CASCADE))

I am trying to migrate the database 

static final Migration MIGRATION_1_2 = new Migration(1, 2) {
        @Override
        public void migrate(SupportSQLiteDatabase database) {
            database.execSQL("What should I do here to add onDelete = CASCADE");
        }
    };
android-room sqlite android
2 Answers

SQLite does not completely support SQL ALTER TABLE standard:

Only the RENAME TABLE, ADD COLUMN, and RENAME COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.

Source

So the only way to add a constraint is recreating the table and copying the data of the old one to the new one:

// 1. Rename your table
database.execSQL("ALTER TABLE Entity RENAME TO Entity_old")
// 2. Recreate the same entity, but now with the constraint
database.execSQL("""
    CREATE TABLE Entity(
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
        parentId INTEGER, 
        someField STRING, 
        CONSTRAINT fk_Entity_entityId_EntityParent_id FOREIGN KEY (entityId) REFERENCES EntityParent(id) ON DELETE CASCADE
    )
""")
// 3. Copy the data from the old table
database.execSQL("INSERT INTO Entity SELECT * FROM Entity_old")
// 4. Delete old table
database.execSQL("DROP TABLE Entity_old")
// 5. Recreate the indices
database.execSQL("CREATE INDEX index_Entity_id ON Entity(id)")
user_20671570

Ok finally I found the solution 

database.execSQL("CREATE TABLE attendance_temp(attendanceId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, time INTEGER NOT NULL, student_id INTEGER, student_name TEXT , roll_number INTEGER, image_path TEXT, info_id INTEGER, present INTEGER , CONSTRAINT fk_class_info FOREIGN KEY (info_id) REFERENCES class_info(id) ON DELETE CASCADE)");
database.execSQL("INSERT INTO attendance_temp SELECT * FROM Attendance");
database.execSQL("DROP TABLE Attendance");
database.execSQL("ALTER TABLE attendance_temp RENAME TO Attendance");
database.execSQL("CREATE INDEX index_Attendance_info_id ON Attendance(info_id)");

Also change the schema in entity class to

@Entity(indices = {@Index(value = {"info_id"})}, foreignKeys = @ForeignKey(entity = StudentClass.class, parentColumns = "id", childColumns = "info_id", onDelete = CASCADE))

The sequence is important.

user_9093170
Related