[SQL] TestDome Solution - Users And Roles

문제

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.

Modify the provided SQL 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.

-- Modify only this SQL create table statement and nothing else
CREATE TABLE users_roles (
  userId INTEGER,
  roleId INTEGER
);

 

 

 

 

답안

CREATE TABLE users_roles (
  userId INTEGER NOT NULL,
  roleId INTEGER NOT NULL,
  CONSTRAINT user_fk FOREIGN KEY (userId) REFERENCES users(id)
  CONSTRAINT role_fk FOREIGN KEY (roleId) REFERENCES roles(id)
  PRIMARY KEY (userId, roleId)
);

• Example case: Correct answer
• Only users from users table can exist: Correct answer ⇒ NOT NULL, FK
• Only roles from the roles table can exist: Correct answer ⇒ NOT NULL, FK
• An user can only have a specific role once: Correct answer ⇒ PK 꼭 지정!

M:N 관계를 위한 조인 테이블을 만들기 위해 FK와 PK를 작성해야하는 문제였다.