androidsqlitedatabase-designandroid-roomandroid-architecture-components

add unique constraint in room database to multiple column


I have one entity in room

@Entity(foreignKeys ={
        @ForeignKey(entity = Label.class, parentColumns = "_id", childColumns = "labelId", onDelete = CASCADE),
        @ForeignKey(entity = Task.class, parentColumns = "_id", childColumns = "taskId", onDelete = CASCADE)
})
public class LabelOfTask extends Data{
    @ColumnInfo(name = "labelId")
    private Integer labelId;
    @ColumnInfo(name = "taskId")
    private Integer taskId;
}

sql syntax of this entity is as below

CREATE TABLE `LabelOfTask` (
    `_id` INTEGER PRIMARY KEY AUTOINCREMENT,
     `labelId` INTEGER,
     `taskId` INTEGER,
     FOREIGN KEY(`labelId`) REFERENCES `Label`(`_id`) ON UPDATE NO ACTION ON DELETE CASCADE ,
     FOREIGN KEY(`taskId`) REFERENCES `Task`(`_id`) ON UPDATE NO ACTION ON DELETE CASCADE
 );

but what change or annotation I need to add in entity class if I want to append below constraint to the auto generated sql schema of the table

unique (labelId, taskId)

Ultimately I want to make combination of labelId and taskId unique in a table(or entity of room) using room library.


Solution

  • A plain UNIQUE constraint on a column, other than via an index, is not supported.

    You can enforce this uniqueness property by setting the unique property of an @Index annotation to true. The following code sample (Java) prevents a table from having two rows that contain the same set of values for the firstName and lastName columns:

    @Entity(indices = {@Index(value = {"first_name", "last_name"},
            unique = true)})
    class User {
        @PrimaryKey
        public int id;
    
        @ColumnInfo(name = "first_name")
        public String firstName;
    
        @ColumnInfo(name = "last_name")
        public String lastName;
    
        @Ignore
        Bitmap picture;
    }
    

    The Kotlin equivalent of the annotation is given below:

    @Entity(indices = [Index(value = ["first_name", "last_name"], unique = true)])
    

    In your code you can do the following changes to have UNIQUE constraints

    @Entity(foreignKeys ={
            @ForeignKey(entity = Label.class, parentColumns = "_id", childColumns = "labelId", onDelete = CASCADE),
            @ForeignKey(entity = Task.class, parentColumns = "_id", childColumns = "taskId", onDelete = CASCADE)},
            indices = {@Index(value = {"labelId", "taskId"},
                    unique = true)}
    )
    public class LabelOfTask extends Data{
        @ColumnInfo(name = "labelId")
        private Integer labelId;
        @ColumnInfo(name = "taskId")
        private Integer taskId;
    }