No "NOT NULL" and "UNIQUE" constraint on Room Persistence Library

Question

While playing with the Room Persistence Library I came to know that there is no methodology to set a data class field with NOT NULL and also UNIQUE constraints. whether SQLite supports those constraints. Isn't it a problem to migrate old database where those constraints are used? Can anyone give a suggestion for this issue?

Solution 1

I came to know that there is no methodology to set a data class field with NOT NULL and also UNIQUE constraints

A @NonNull annotation on an @Entity field will cause that field's column to have NOT NULL applied to it.

unique=true on an @Index will enforce a uniqueness constraint (e.g., @Entity(indices={@Index(value="something", unique=true)}). However, you are correct that a plain UNIQUE constraint on a column, other than via an index, is not supported.

Isn't it a problem to migrate old database where those constraints are used?

Room is not designed to support existing database structures, particularly in the now-current alpha state. Over time, I would expect Room to support a higher percentage of SQLite features, though I will be stunned if it ever reaches 100%.

Solution 2

Complementary answer about NOT NULL for those using Kotlin:

please note that marking a type as non optional will automatically make it not null (and an optional type will not do that).

You can check it in the schema generated by room with @Database(exportSchema = true) on your database.

For example I have something like that:

@Entity(tableName = "messages")
data class Message (
        @PrimaryKey
        val messageId: UUID = UUID.randomUUID(),
        val date: Date = Date(),
        val receivedDate: Date? = null
)

And in the generated schema I can read:

"CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`messageId` TEXT NOT NULL, `date` INTEGER NOT NULL, `receivedDate` INTEGER, PRIMARY KEY(`messageId`))"

(Note: the Date type is here an Int and the UUID a string due to converters I use elsewhere)

Solution 3

If you have multiple item that is to be marked unique & based on that you want to insert in db then you can use composite primary key.

For Not null, Room has provided "@NonNull" annotation that is added over the field that cannot be null.

In the below mentioned eg. roll number is unique in each class but 2 different class can have same roll number. So we can use class & rollNumber as composite primary key & insert in db uniquely. Example:

    @Entity(primaryKeys = {"rollNumber", "class"})   
    class Student {
       @NonNull
        private int rollNumber;
        private String firstName;
        private String lastName;
        private int class;
        } 

Solution 4

for a null able field you can use wrapper primitive type java. for example use Integer instance int in your Room Table. as in wrapper primitive type java this can bee null but primitive type class cant bee null. and in generation of this SQL code for primitive field that use notNull=true but when use Integer in generayion SQL code use notNull=false

View more solutions