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.

A plain UNIQUE constraint on a column, other than via an index, is not supported. — lib4backer, Feb 24 '18 at 12:13

score 117 · Accepted Answer · edited Feb 15 '21 at 21:01

117

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

edited Feb 15 '21 at 21:01

Chisko

3,092
6
27
45

answered Feb 24 '18 at 12:16

lib4backer

3,337
3
18
16

5

What about setting a single property as UNIQUE ? – zulkarnain shah Jul 23 '18 at 09:35
1

I have tried to answer your question, but I think I paste in wrong place :) Please check it below :D – Ercan Mar 06 '19 at 12:48
1

Hi! What happens if you try to add a second identical value? – Valeriy Jun 26 '19 at 13:27
1

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)] – Jamal S Mar 06 '20 at 20:26
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 Jun 01 '20 at 05:50
@Valeriy a conflict will occur and will based on your defined conflict strategy. – Irfan Ul Haq Jun 02 '21 at 09:35
I wonder why Android room API doesn't support a single column unique constraint such as adding `@Unique` annotation to the column definition. – tonga Apr 14 '22 at 16:21

score 53 · Answer 2 · edited Feb 20 '20 at 10:18

53

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

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

edited Feb 20 '20 at 10:18

parohy

2,070
2
22
38

answered Oct 18 '18 at 12:33

Ercan

2,601
22
23

How to create composite unique key and composite primary key ?? – K Pradeep Kumar Reddy Jun 01 '20 at 05:52
@KPradeepKumarReddy there's a primaryKeys field in the Entity annotation for that – Afzal N Aug 04 '20 at 22:40

score 13 · Answer 3 · answered Nov 26 '20 at 16:32

13

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

answered Nov 26 '20 at 16:32

Masum

4,879
2
23
28

score 3 · Answer 4 · answered Feb 06 '22 at 20:06

3

Can't comment, so I'll just add it here. 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.

answered Feb 06 '22 at 20:06

Christopher Myers

89
1
5

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. – Gert Arnold Feb 06 '22 at 20:25
@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. – Christopher Myers Feb 06 '22 at 20:38
The caveat isn't necessary. It's off-topic. Everything is about generated migration code here. – Gert Arnold Feb 06 '22 at 20:48

add unique constraint in room database to multiple column

4 Answers4

Linked

Related