CODEWITHSUNDEEP
androidsqliterelational-databaseandroid-room
Andrei Ionescu
Andrei Ionescu

Reputation: 43

Can I add check constraints in Room?

In my database I have 3 tables: problems, users and roles. The roles table has 2 values: client and coach.

Database rules:

  1. An user has only one role (either client or coach).
  2. A problem has one coach and one client.

The problems entity has two foreign keys to the users table: coach_id and client_id. Can I add check constraints using Android Room to be sure that a problem respects this rule ?

I want to avoid the cases where a problem has two clients or two coaches.

Database Conceptual Diagram

enter image description here

Upvotes: 1

Views: 1101

Answers (1)

MikeT
MikeT

Reputation: 57043

Currently ROOM doesn't support the addition of CHECK constraints.

However, you could introduce a CHECK constraint by DROPPING the table and then CREATEing the table (probably not desirable as that could introduce ongoing complications e.g. when migrating).

  • However, CHECK constraints are limited and cannot include sub queries which, I believe, would complicate matters and perhaps even rule out there being a suitable/usable CHECK constraint.

    • I believe that you would be looking at something like coach INTEGER CHECK((SELECT authority FROM user JOIN role ON role.roleid = user.role) = 0) REFERENCES user(userid) ON DELETE CASCADE ON UPDATE CASCADE, (assuming, as it's unclear what column denotes the role type, that a coach has to have a role who's authority is 0). However, the CHECK cannot be used because it would result in a subqueries prohibited in CHECK constraints error.

As such it might be simpler to programatically do such a check as part of the process of inserting a Problem. That is for each type (coach/client) get the role from the user and reject the actual insertion into the database should the incorrect role be specified. This being more akin to Room's OO stance (i.e. tables support and are created according to Objects).

Example

Perhaps consider the following based upon your question:-

Role.java (Role Table)

@Entity(tableName = "role")
public class Role {

    public static final int AUTHORITY_COACH = 0;
    public static final int AUTHORITY_CLIENT = 1;

    @PrimaryKey
    Long roleid;
    String description;
    int authority;

    public Role(String description, int authority) {
        this.description = description;
        if ( authority >=  AUTHORITY_CLIENT) {
            this.authority = AUTHORITY_CLIENT;
        } else {
            this.authority = AUTHORITY_COACH;
        }
    }

    public Long getRoleid() {
        return roleid;
    }

    public void setRoleid(Long roleid) {
        this.roleid = roleid;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public int getAuthority() {
        return authority;
    }

    public void setAuthority(int authority) {
        this.authority = authority;
    }
}

User.java (User table)

@Entity(
        tableName = "user",
        foreignKeys = {
                @ForeignKey(
                        entity = Role.class,
                        parentColumns = {"roleid"},
                        childColumns = {"role"},
                        onDelete = ForeignKey.CASCADE,
                        onUpdate = ForeignKey.CASCADE
                        )
        },
        indices = {
                @Index("role")
        }
)
public class User {

    @PrimaryKey
    Long userid;
    String name;
    String surname;
    String username;
    String email;
    String password;
    long role;

    public User(String name, String surname, String username, String email, String password, long role) {
        this.name = name;
        this.surname = surname;
        this.username = username;
        this.email = email;
        this.password = password;
        this.role = role;
    }

    public Long getUserid() {
        return userid;
    }

    public void setUserid(Long userid) {
        this.userid = userid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSurname() {
        return surname;
    }

    public void setSurname(String surname) {
        this.surname = surname;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public long getRole() {
        return role;
    }

    public void setRole(long role) {
        this.role = role;
    }
}

Problem.java (Problem Table)

@Entity(
        tableName = "problem",
        foreignKeys = {
                @ForeignKey(
                        entity = User.class,
                        parentColumns = {"userid"},
                        childColumns = {"coach"},
                        onDelete = ForeignKey.CASCADE,
                        onUpdate = ForeignKey.CASCADE
                ),
                @ForeignKey(
                        entity = User.class,
                        parentColumns = {"userid"},
                        childColumns = {"client"},
                        onDelete = ForeignKey.CASCADE,
                        onUpdate = ForeignKey.CASCADE
                )
        },
        indices = {
                @Index(value = "coach"),
                @Index(value = "client")
        }
)
public class Problem {

    @PrimaryKey
    Long problemid;
    String title;
    String status;
    long coach;
    long client;

    public Problem(){}

    public Problem(String title, String status, User coach, User client) {
        this.title = title;
        this.status = status;
        this.coach = coach.getUserid();
        this.client = client.getUserid();
    }

    public Long getProblemid() {
        return problemid;
    }

    public void setProblemid(Long problemid) {
        this.problemid = problemid;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public long getCoach() {
        return coach;
    }

    public void setCoach(long coach) {
        this.coach = coach;
    }

    public long getClient() {
        return client;
    }

    public void setClient(long client) {
        this.client = client;
    }
}

UserWithRole.java (POJO to combine User with the User's role)

public class UserWithRole {

    @Embedded
    User user;
    @Relation(entity = Role.class,entityColumn = "roleid",parentColumn = "role")
    Role userrole;

    public UserWithRole(){}

    public User getUser() {
        return user;
    }

    public Role getUserrole() {
        return userrole;
    }
}
  • Not actually used (but could be used to do the CHECK)

AllDao.java (Data Access all combined)

@Dao
public interface AllDao {

    @Insert
    long insertRole(Role role);

    @Insert
    long insertUser(User user);

    @Insert
    long insertProblem(Problem problem);

    @Query("SELECT (authority = " + Role.AUTHORITY_COACH + ") FROM user JOIN role ON role.roleid = user.role WHERE userid = :userid ")
    boolean isUserACoach(long userid);

    @Query("SELECT * FROM user WHERE userid = :userid")
    User getUserById(long userid);

}
  • the isUuserACoach being the important query that replicates the CHECK constraint assumed above.

Database.java (Room @Database)

@androidx.room.Database(version = 1,entities = {Role.class,User.class,Problem.class})
public abstract class Database extends RoomDatabase {

    abstract AllDao allDao();
}

MainActivity.java (test it all out)

public class MainActivity extends AppCompatActivity {
    Database database;
    AllDao allDao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        database = Room.databaseBuilder(
                this,
                Database.class,
                "mydb"
        )
                .allowMainThreadQueries()
                .build();
        allDao = database.allDao();

        long coachRole = allDao.insertRole(new Role("Coach",Role.AUTHORITY_COACH));
        long newClientRole = allDao.insertRole(new Role("New Client",Role.AUTHORITY_CLIENT));
        long oldClientRole = allDao.insertRole(new Role("Old Client",Role.AUTHORITY_CLIENT));

        long fredBloggs = allDao.insertUser(new User("Fred","Bloggs","fblog","[email protected]","password",coachRole));
        long marySmith = allDao.insertUser(new User("Mary","Smith","msmith","[email protected]","password",newClientRole));
        long anneWalker = allDao.insertUser(new User("Anne","Walker","awalker","[email protected]","password",oldClientRole));

        //<<<<<<<<<< SHOULD BE OK TO ADD >>>>>>>>>>
        if (verifyAndAddProblem("Problem 1","new",allDao.getUserById(fredBloggs),allDao.getUserById(marySmith)) > 0) {
            Log.d("PROBLEMADDRESULT","Problem Added");
        } else {
            Log.d("PROBLEMADDRESULT","Problem not added");
        }

        //<<<<<<<<<< SHOULD NOT BE ADDED (annWalker is NOT a coach) >>>>>>>>>>
        if (verifyAndAddProblem("Problem 2","new",allDao.getUserById(anneWalker),allDao.getUserById(marySmith)) > 0) {
            Log.d("PROBLEMADDRESULT","Problem Added");
        } else {
            Log.d("PROBLEMADDRESULT","Problem not added");
        }

        //<<<<<<<<<< SHOULD NOT BE ADDED (fredBloggs is a coach BUT is NOT a client) >>>>>>>>>>
        if (verifyAndAddProblem("Problem 3","new",allDao.getUserById(fredBloggs),allDao.getUserById(fredBloggs)) > 0) {
            Log.d("PROBLEMADDRESULT","Problem Added");
        } else {
            Log.d("PROBLEMADDRESULT","Problem not added");
        }
    }

    //ALTERNATIVE TO CHECK CONSTRAINT DONE HERE
    private long verifyAndAddProblem(String title, String status, User coach, User client) {

        long rv = -1;
        if (!allDao.isUserACoach(coach.getUserid()) || allDao.isUserACoach(client.getUserid())) return rv;
        return allDao.insertProblem(new Problem(title,status,coach,client));
    }
}

When run log contains :-

2020-01-02 08:47:33.647 D/PROBLEMADDRESULT: Problem Added
2020-01-02 08:47:33.658 D/PROBLEMADDRESULT: Problem not added
2020-01-02 08:47:33.661 D/PROBLEMADDRESULT: Problem not added

Upvotes: 2

Related Questions