Users And Roles


SQL Constraints Public New

Hard  

5min

The following two tables are used to define users and their respective roles:

TABLE users
  id INTEGER NOT NULL PRIMARY KEY,
  userName VARCHAR(50) NOT NULL

TABLE roles
  id INTEGER NOT NULL PRIMARY KEY,
  role VARCHAR(20) NOT NULL

The users_roles table should contain the mapping between each user and their roles. Each user can have many roles, and each role can have many users.

Improve the provided create table statement so that:

  • Only users from the users table can exist within users_roles.
  • Only roles from the roles table can exist within users_roles.
  • A user can only have a specific role once.

See the example case for more details.

SQLite 3.19.3
   

  •   Example case: Wrong answer
  •   Only users from users table can exist: Wrong answer
  •   Only roles from the roles table can exist: Wrong answer
  •   A user can only exist within a role once: Wrong answer