add unique constraint in room database to multiple column

Question

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 1

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

Solution 2

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

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

Solution 3

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

Comments

[lib4]

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

[Marian Paździoch]

index? you mean primary key?

[zulkarnain shah]

What about setting a single property as UNIQUE ?

[Ercan]

I have tried to answer your question, but I think I paste in wrong place :) Please check it below :D

[Valeriy]

Hi! What happens if you try to add a second identical value?

[Jamal S]

For Kotlin you can write it this way: foreignKeys = [ForeignKey( entity = Label::class, parentColumns = ["_id"], childColumns = ["labelId"], onDelete = ForeignKey.CASCADE)], indices = [Index(value = ["labelId", "taskId"], unique = true)]

[K Pradeep Kumar Reddy]

Is this the example of creating the composite unique key using two columns (first_name, last_name) ?? or Is it the example of creating two separate unique keys using two different columns ??

[K Pradeep Kumar Reddy]

How to create composite unique key and composite primary key ??

[Afzal N]

@KPradeepKumarReddy there's a primaryKeys field in the Entity annotation for that

[Irfan Ul Haq]

@Valeriy a conflict will occur and will based on your defined conflict strategy.

[Gert Arnold]

There's a reason why commenting is blocked for < 50 reputation users. I thought it was pretty obvious that posting answers is not offered as a way to evade this restriction.

[Christopher Myers]

@GertArnold I'm sure it isn't, and I could write a complete answer that also adds the caveat I just mentioned, but I personally don't like when the same answer is repeated multiple times (especially when it's clear your answer is weeks or months later), so I just listed the caveat, which might not answer the original question, but it does relate to it and other questions asked. And yes, comments aren't for discussion either.

[Gert Arnold]

The caveat isn't necessary. It's off-topic. Everything is about generated migration code here.

[tonga]

I wonder why Android room API doesn't support a single column unique constraint such as adding @Unique annotation to the column definition.