CODEWITHSUNDEEP
androidsqlitedatabase-designandroid-roomandroid-architecture-components
Kevan
Kevan

Reputation: 1195

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.

Upvotes: 71

Views: 56057

Answers (5)

Christopher Myers
Christopher Myers

Reputation: 107

If manually migrating your Room DB to add an index using SQL statements, your index name to be able to match the annotation on the table must be of the format:

...
database.execSQL("CREATE UNIQUE INDEX index_tableName_columnName ON tableName (columnName)")
...

Not sure how long the index names can be, so not sure what it looks like with multi-column indexes.

Upvotes: 5

Micer
Micer

Reputation: 8979

You can also achieve multi column uniqueness by composite primary key (entity is uniquely identified by a combination of multiple columns).

@Entity(primaryKeys = ["firstName", "lastName"])
data class User(
    val firstName: String?,
    val lastName: String?
)

See official docs

Note that in this case you cannot use auto-generated increment of id, SQLite doesn't support that.

Upvotes: 1

AnasAbubacker
AnasAbubacker

Reputation: 3607

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;
}

Upvotes: 128

Masum
Masum

Reputation: 4969

For a single column Uniqueness

@Entity(indices = {@Index(value = {"first_name"},unique = true)})

For Multiple column Uniqueness

@Entity(indices = {@Index(value = {"first_name", "last_name"},unique = true)})

Upvotes: 14

Ercan
Ercan

Reputation: 2811

If you wonder to make a single column to be unique, only need to write

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

Upvotes: 60

Related Questions