[쿼리테스트] DDL 연습

Hyunjun Kim·2026년 2월 9일

SQL

목록 보기

98/98

문제

다음 두 테이블이 있다.

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

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

usersRoles 테이블은 사용자와 역할(roles) 사이의 다대다(M:N) 관계를 표현한다.
다음 조건을 만족하도록 usersRoles 테이블 생성문을 수정하라(오직 이 CREATE 문장만 수정).

요구사항

usersRoles에는 반드시 users 테이블에 존재하는 사용자만 들어갈 수 있어야 한다.
usersRoles에는 반드시 roles 테이블에 존재하는 역할만 들어갈 수 있어야 한다.
동일 사용자에 대해 동일 역할은 중복해서 들어갈 수 없어야 한다. (즉 (userId, roleId) 조합은 유일해야 함)

초기 미완성 문장:

-- 수정 전 (변경 금지)
CREATE TABLE usersRoles (
  userId INTEGER,
  roleId INTEGER
);

✅ 정답 DDL (안전·권장 버전)

CREATE TABLE usersRoles (
  userId  INT NOT NULL,
  roleId  INT NOT NULL,
  PRIMARY KEY (userId, roleId),                     -- (userId, roleId) 조합을 유일하게 보장
  CONSTRAINT fk_usersroles_user FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_usersroles_role FOREIGN KEY (roleId) REFERENCES roles(id) ON DELETE RESTRICT
);

설명 요지:

NOT NULL로 비어있는 값 금지.

PRIMARY KEY (userId, roleId)로 동일 조합의 중복 삽입을 막음.

FOREIGN KEY로 참조 무결성 보장(사용자·역할이 실제로 존재해야 함).

ON DELETE 동작은 설계 정책에 따라 CASCADE(사용자 삭제 시 매핑 자동 삭제) 또는 RESTRICT/NO ACTION(참조 존재하면 삭제 금지)를 선택할 수 있다.

🧩 동작(예시)

1) 샘플 데이터

INSERT INTO users (id, userName) VALUES (1, 'alice'), (2, 'bob');
INSERT INTO roles (id, role) VALUES (10, 'admin'), (20, 'editor');

2) 정상 삽입

INSERT INTO usersRoles (userId, roleId) VALUES (1, 10); -- 성공
INSERT INTO usersRoles (userId, roleId) VALUES (1, 20); -- 성공

3) 중복 삽입 시도 → 실패

INSERT INTO usersRoles (userId, roleId) VALUES (1, 10);
-- 에러: 중복된 PRIMARY KEY (userId, roleId) 이므로 삽입 불가

4) 존재하지 않는 사용자/역할로 삽입 시도 → 실패

INSERT INTO usersRoles (userId, roleId) VALUES (999, 10);
-- 에러: fk_usersroles_user 참조 위배 (users.id에 999 없음)

5) 사용자 삭제 동작 (ON DELETE CASCADE 사용 시)

DELETE FROM users WHERE id = 1;
-- usersRoles에 (1,10)·(1,20) 행이 자동으로 삭제된다 (CASCADE)

💡 설계·운영상 고려사항 (꼭 읽을 것)

A. Composite PK vs Surrogate id

PRIMARY KEY (userId, roleId) (합성 PK)
- 장점: 중복 방지 간단, 추가 인덱스 불필요, 조회(특정 유저의 역할 등)에 효율적이다.
- 단점: 만약 이 매핑에 대해 추가 메타데이터(예: 할당일, 권한 레벨 등)를 자주 붙이면 별도의 id가 편리할 수 있다.
대안으로 id(AUTO_INCREMENT)를 추가하고 (userId, roleId)에 UNIQUE 제약을 둘 수도 있다.

B. 외래키의 ON DELETE / ON UPDATE 정책

ON DELETE CASCADE: 사용자(또는 역할) 삭제 시 매핑도 자동 삭제. 편리하지만 실수로 사용자 삭제하면 매핑도 날아간다.
ON DELETE RESTRICT 또는 기본 NO ACTION: 참조가 있으면 사용자를 못 지우게 해서 데이터 무결성을 강하게 보장.
정책은 도메인 규칙에 따라 선택한다(권장: 운영 정책에 맞춰 명시적으로 결정).

C. InnoDB 요구

MySQL 사용 시 외래키 제약을 쓰려면 테이블 엔진이 InnoDB이어야 한다. (MyISAM은 FK 무시)

D. 성능 고려

합성 PK를 사용하면 (userId, roleId)로 하는 조회(특정 user의 role 체크, 특정 role의 user 체크)에 적절한 인덱스가 이미 존재한다.
반대로 자주 role → user 대량 조회가 있고 정렬·조인 패턴이 복잡하면 추가 복합 인덱스 고려.

🔍 자주 묻는 질문(FAQ)

Q1. usersRoles에 NULL을 허용해도 되나?
A1. 권장하지 않는다. userId와 roleId가 NULL이면 의미 없는 매핑이므로 NOT NULL로 강제한다.

Q2. 복합 PK 대신 id + UNIQUE (userId, roleId)를 쓰는 이유는?
A2. 매핑에 추가 컬럼(할당일, 활성여부 등)이 많아지거나 외부에서 매핑 row를 고유하게 참조해야 하는 경우 id가 유용하다.

Q3. FK의 ON DELETE는 어떤게 안전한가?
A3. 안전성 관점에서는 RESTRICT(삭제 차단)가 더 보수적이다. 자동 청소(CASCADE)가 편리하지만 의도치 않은 데이터 삭제 위험이 있다.

요약

다대다 관계 테이블은 NOT NULL + FOREIGN KEY로 참조 무결성을 보장하고, (userId, roleId)에 복합 기본키(또는 UNIQUE)를 두어 동일 매핑의 중복 삽입을 방지해야 한다.

Hyunjun Kim

Data Analytics Engineer 가 되

이전 포스트