관계와 식별자 - 카디널리티 선택성 식별 비식별 관계와 주식별자 (SQLD 기본 3장)
이 장에서 배우는 것
2장에서 엔터티와 속성을 나눴다. 엔터티는 혼자 있지 않다. 사원은 부서에 속하고, 주문 상세는 주문에 딸려 있다. 이 장은 엔터티 사이의 관계와, 인스턴스를 하나로 가려내는 식별자를 다룬다. 관계를 어떻게 읽느냐에 따라 외래 키가 NULL 을 허용할지, 부모를 지울 때 자식이 어떻게 될지가 정해진다.
- 관계의 차수(1:1, 1:N, M:N)와 선택성(필수·선택)을 읽고, M:N 을 관계 엔터티로 푸는 이유를 정리한다.
- 식별 관계와 비식별 관계가 자식 테이블의 기본 키 구성을 어떻게 바꾸는지 확인한다.
- 주식별자의 네 가지 성질(유일성·최소성·불변성·존재성)과, 인조 식별자를 쓸 때 반드시 함께 둬야 하는 제약을 실행으로 확인한다.
핵심 개념
퇴사한 직원의 부서를 지웠더니 그 부서 사원들의 부서 칸이 가리키는 곳이 사라졌다는 사고는 흔하다. 반대로 부서를 못 지우게 막아 두었더니 조직 개편 때마다 DBA 를 불러야 한다는 불만도 흔하다. 둘 다 관계를 설계할 때 "부모가 없어지면 자식은 어떻게 되는가"를 정하지 않아서 생긴다.
관계의 표기: 차수와 선택성
관계는 두 엔터티 사이의 연관이다. 관계를 읽을 때는 두 가지를 본다. 차수(카디널리티)는 한쪽 인스턴스 하나에 다른 쪽이 몇 개 대응하는지(1:1, 1:N, M:N)다. 선택성(참여도)은 대응이 반드시 있어야 하는지(필수) 없어도 되는지(선택)다. "부서 하나에는 사원이 여러 명 있을 수 있고, 사원은 부서가 없을 수도 있다"는 문장에서 앞은 차수, 뒤는 선택성이다.
그림 · IE 표기로 읽는 관계의 차수와 선택성 — 선 끝의 기호가 상대 쪽 개수를 말한다. 세로 막대는 하나(필수), 동그라미는 없어도 됨(선택), 까마귀발은 여럿이다. 비식별 관계는 점선, 식별 관계는 실선으로 그린다.
관계를 종류로 나누면 존재에 의한 관계(사원은 부서에 소속된다)와 행위에 의한 관계(고객이 주문한다)가 있다. ERD 에서는 선 하나로 그리지만, UML 클래스 다이어그램은 연관과 의존으로 구분해 그린다.
M:N 관계는 테이블 두 개로 표현할 수 없다. 학생 하나가 여러 과목을, 과목 하나가 여러 학생을 가지므로, 어느 쪽에 외래 키를 둬도 값이 여러 개가 된다. 그래서 1장의 수강(enroll)처럼 관계 자체를 엔터티로 만들어 1:N 두 개로 푼다.
식별 관계와 비식별 관계
| 구분 | 식별 관계 | 비식별 관계 |
|---|---|---|
| 부모 키의 위치 | 자식의 기본 키 일부가 된다 | 자식의 일반 속성(외래 키)이 된다 |
| 예 | 주문행(주문번호, 행번호) | 사원(사원번호, ..., 부서번호) |
| 자식이 부모 없이 존재 | 불가능 | 선택 관계면 가능(외래 키 NULL) |
| 장점 | 부모 키로 자식을 바로 찾는다. 조인이 줄어든다 | 자식 키가 짧고, 부모가 바뀌어도 자식 키는 그대로다 |
| 단점 | 단계가 깊어지면 자식 기본 키가 계속 길어진다 | 부모 정보를 보려면 조인이 필요하다 |
그림 · 식별 관계와 비식별 관계에서 부모 키가 옮겨 가는 자리 — 식별 관계에서는 부모 키 order_id 가 자식 기본 키의 일부가 되고, 비식별 관계에서는 dept_id 가 기본 키 밖의 외래 키가 된다. 그래서 비식별·선택 관계의 자식은 부모 없이(NULL) 들어갈 수 있다.
식별자의 분류와 주식별자의 성질
식별자는 대표성(주식별자·보조식별자), 생성 방식(내부식별자·외부식별자), 속성 수(단일·복합), 대체 여부(본질식별자·인조식별자)로 나눈다. 주식별자로 고른 것은 네 가지 성질을 가져야 한다.
- 유일성: 모든 인스턴스를 서로 구별한다.
- 최소성: 유일성을 지키는 데 필요한 최소한의 속성만 쓴다.
- 불변성: 한번 정하면 값이 바뀌지 않는다.
- 존재성: 값이 반드시 있다(NULL 불가).
인조식별자(자동 증가 번호)는 짧고 바뀌지 않아 편하다. 그러나 인조식별자만 두면 본질적으로 같은 인스턴스가 중복 저장되는 것을 막지 못한다. 아래에서 직접 확인한다.
예제 스키마
부서와 사원은 비식별·선택 관계다(사원의 dept_id 는 NULL 가능). 주문과 주문행은 식별 관계다(order_line 의 기본 키에 order_id 가 들어 있다). SQLite 는 외래 키 검사가 기본으로 꺼져 있어서 스키마 첫 줄에서 켰다.
-- 3장: 부서·사원(비식별 관계)과 주문·주문행(식별 관계). SQLite 는 연결마다 외래 키 검사를 켜야 한다.
PRAGMA foreign_keys = ON;
CREATE TABLE dept (
dept_id INTEGER PRIMARY KEY,
dept_name TEXT NOT NULL
);
CREATE TABLE emp (
emp_id INTEGER PRIMARY KEY,
emp_name TEXT NOT NULL,
dept_id INTEGER REFERENCES dept(dept_id)
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
ordered_on TEXT NOT NULL
);
CREATE TABLE order_line (
order_id INTEGER NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
line_no INTEGER NOT NULL,
item TEXT NOT NULL,
PRIMARY KEY (order_id, line_no)
);
INSERT INTO dept VALUES (1, '총무'), (2, '연구'), (3, '품질');
INSERT INTO emp VALUES (10, '이서', 1), (11, '고은', 2), (12, '나래', NULL);
INSERT INTO orders VALUES (500, '2026-09-10'), (501, '2026-09-11');
INSERT INTO order_line VALUES (500, 1, '모니터'), (500, 2, '키보드'), (501, 1, '마우스');| emp_id | emp_name | dept_id |
|---|---|---|
| 10 | 이서 | 1 |
| 11 | 고은 | 2 |
| 12 | 나래 | NULL |
SQL과 실행 결과
없는 부모를 가리키는 자식은 거부된다
INSERT INTO emp VALUES (13, '다인', 9);실행 결과:
Runtime error near line 1: FOREIGN KEY constraint failed (19)선택 관계: 부모 없이 들어가는 자식
외래 키에 NULL 을 넣는 것은 "부모가 없다"는 뜻이라 허용된다. 비식별·선택 관계이기 때문이다.
INSERT INTO emp VALUES (13, '다인', NULL);
SELECT e.emp_name, d.dept_name
FROM emp e LEFT JOIN dept d ON d.dept_id = e.dept_id
ORDER BY e.emp_id;실행 결과:
emp_name dept_name
-------- ---------
이서 총무
고은 연구
나래 NULL
다인 NULL자식이 있는 부모 삭제: 기본 동작은 거부
총무 부서(1)에는 사원 이서가 있다. 외래 키에 삭제 규칙을 따로 적지 않았으므로 삭제가 거부된다.
DELETE FROM dept WHERE dept_id = 1;실행 결과:
Runtime error near line 1: FOREIGN KEY constraint failed (19)식별 관계와 CASCADE
주문행은 주문 없이 의미가 없으므로 ON DELETE CASCADE 를 걸었다. 주문 500 을 지우면 그 주문행 두 개도 함께 지워진다.
DELETE FROM orders WHERE order_id = 500;
SELECT order_id, line_no, item FROM order_line;실행 결과:
order_id line_no item
-------- ------- ------
501 1 마우스식별 관계의 자식은 부모 키가 기본 키의 일부라서 같은 주문 안에서 행 번호가 겹칠 수 없다.
INSERT INTO order_line VALUES (501, 2, '패드');
INSERT INTO order_line VALUES (501, 1, '케이블');실행 결과:
Runtime error near line 2: UNIQUE constraint failed: order_line.order_id, order_line.line_no (19)첫 INSERT(501, 2)는 성공했고 두 번째(501, 1)가 기존 행과 겹쳐 거부됐다.
인조식별자만으로는 중복을 못 막는다
CREATE TABLE member_loose (
member_id INTEGER PRIMARY KEY,
email TEXT NOT NULL
);
INSERT INTO member_loose (email) VALUES ('ari@example.com');
INSERT INTO member_loose (email) VALUES ('ari@example.com');
SELECT member_id, email FROM member_loose;실행 결과:
member_id email
--------- ---------------
1 ari@example.com
2 ari@example.com같은 이메일의 회원이 두 명이 됐다. 번호는 유일하지만 "회원"은 유일하지 않다. 본질식별자 후보(이메일)에 UNIQUE 를 함께 걸어야 한다.
CREATE TABLE member_safe (
member_id INTEGER PRIMARY KEY,
email TEXT NOT NULL UNIQUE
);
INSERT INTO member_safe (email) VALUES ('ari@example.com');
INSERT INTO member_safe (email) VALUES ('ari@example.com');실행 결과:
Runtime error near line 6: UNIQUE constraint failed: member_safe.email (19)표 · 외래 키·기본 키가 실제로 막은 일과 허용한 일
| 시도 | 종료 코드 | 실제 결과 |
|---|---|---|
| 없는 부서 9 를 가리키는 사원 추가 | 1 | Runtime error near line 1: FOREIGN KEY constraint failed (19) |
| 부서 없이(NULL) 사원 추가 — 비식별·선택 관계 | 0 | 성공, 사원 4명 중 부서 NULL 2명 |
| 사원이 있는 부서 1 삭제 | 1 | Runtime error near line 1: FOREIGN KEY constraint failed (19) |
| 주문 500 삭제 — 식별 관계 + CASCADE | 0 | 남은 주문행 1개 (500 의 행도 함께 삭제) |
| 같은 (주문, 행번호) 다시 추가 | 1 | Runtime error near line 2: UNIQUE constraint failed: order_line.order_id, order_line.line_no (19) |
| 인조식별자만 두고 같은 이메일 두 번 | 0 | 성공, 2행 — 중복을 못 막는다 |
| 이메일에 UNIQUE 를 더하고 같은 시도 | 1 | Runtime error near line 6: UNIQUE constraint failed: member_safe.email (19) |
표준과 구현의 차이
아래 Oracle·SQL Server 동작은 비교 설명이며 이 책에서 실행하지 않았다.
| 항목 | SQLite(실행) | Oracle | SQL Server |
|---|---|---|---|
| 외래 키 검사 | 연결마다 PRAGMA foreign_keys = ON 필요 | 항상 검사 | 항상 검사 |
| ON DELETE 옵션 | NO ACTION, RESTRICT, CASCADE, SET NULL, SET DEFAULT | 기본(거부), CASCADE, SET NULL | NO ACTION, CASCADE, SET NULL, SET DEFAULT |
| 자동 증가 키 | INTEGER PRIMARY KEY | 시퀀스, IDENTITY 열 | IDENTITY 속성, 시퀀스 |
| ON UPDATE CASCADE | 지원 | 지원하지 않음 | 지원 |
[구현 차이] SQLite 에서 외래 키 검사를 끄면 없는 부서를 가리키는 사원이 들어가고, 주문을 지워도 주문행이 남는다. 모델에서 정한 관계가 DB 에서 지켜지는지는 제품 설정까지 확인해야 안다.
PRAGMA foreign_keys = OFF;
INSERT INTO emp VALUES (13, '다인', 9);
DELETE FROM orders WHERE order_id = 500;
SELECT (SELECT COUNT(*) FROM emp WHERE dept_id = 9) AS orphan_emp,
(SELECT COUNT(*) FROM order_line WHERE order_id = 500) AS orphan_lines;실행 결과:
orphan_emp orphan_lines
---------- ------------
1 2시험에서 헷갈리는 지점
판단 1. "비식별 관계에서 자식의 외래 키는 항상 NOT NULL 이다"
틀렸다. 비식별 관계는 필수일 수도 선택일 수도 있다. 선택 관계면 외래 키가 NULL 일 수 있고, 위 예제의 나래·다인이 그 경우다. 식별 관계의 외래 키는 기본 키의 일부이므로 NULL 이 될 수 없다.
판단 2. "식별 관계를 계속 이어 가면 하위 엔터티의 기본 키 속성 수가 늘어난다"
맞다. 주문 → 주문행 → 주문행 옵션처럼 식별 관계가 세 단계면 맨 아래 기본 키는 세 컬럼 이상이 된다. 조인 조건과 SQL 이 길어지는 것이 식별 관계의 대표적 단점이다. 그래서 자식이 부모와 독립적으로 쓰이거나 부모 키가 너무 길면 비식별 관계를 검토한다.
판단 3. "주식별자는 유일하기만 하면 속성을 많이 포함해도 된다"
틀렸다. 최소성을 어긴다. (사원번호, 이름)은 유일하지만 사원번호만으로도 유일하므로 이름은 불필요하다.
판단 4. "M:N 관계는 물리 모델에서 테이블 두 개와 외래 키 하나로 구현한다"
틀렸다. 관계 엔터티(교차 테이블)를 추가해 테이블 세 개로 구현한다.
연습 문제
- "학생은 동아리에 가입하지 않아도 되고 여러 동아리에 가입할 수 있다. 동아리에는 회원이 한 명 이상 있어야 한다." 이 문장의 차수와 양쪽 선택성을 쓰고, 테이블 몇 개로 구현하는지 쓰라.
- 예제 스키마에서 품질 부서(3)를 지운 뒤 부서 수를 조회하라. 삭제가 성공하는지 먼저 판단하라.
- 주민등록번호를 회원의 주식별자로 쓰지 않는 이유를 주식별자의 성질과 관련해 두 가지 쓰라.
- 식별 관계인 주문–주문행에서 주문번호를 바꿔야 하는 업무 요구가 생겼다. 어떤 일이 벌어지는가?
- 인조식별자
member_id를 기본 키로 쓰는 회원 테이블에 반드시 함께 둬야 하는 제약은 무엇이고, 왜 필요한가?
정답과 해설
1. 차수 M:N. 학생 쪽 참여는 선택, 동아리 쪽 참여는 필수. 학생·동아리·가입(학생번호, 동아리번호) 세 테이블로 구현한다. "동아리에 한 명 이상"이라는 필수 조건은 외래 키만으로 강제할 수 없어 애플리케이션이나 트리거로 지킨다.
2. 성공한다. 품질 부서에는 사원이 없으므로 외래 키가 막을 것이 없다.
DELETE FROM dept WHERE dept_id = 3;
SELECT COUNT(*) AS depts FROM dept;실행 결과:
depts
-----
23. 존재성: 외국인 등 번호가 없는 회원이 있을 수 있다. 불변성: 정정·변경되는 경우가 있다. 그 밖에 개인정보라 저장과 노출을 최소화해야 한다는 법적 제약도 있다.
4. 주문번호가 주문행 기본 키의 일부이므로 부모와 모든 자식 행의 키를 함께 바꿔야 한다. 주식별자의 불변성을 요구하는 이유가 이것이다. 이런 요구가 예상되면 주문번호를 업무 번호(보조식별자)로 두고, 바뀌지 않는 인조식별자를 주식별자로 쓰는 설계를 검토한다.
5. 본질식별자 후보(예: 이메일, 로그인 아이디)에 대한 UNIQUE 제약. 인조식별자는 행마다 새 번호를 주므로 같은 사람이 두 번 저장되어도 막지 못한다. 위 member_loose 예제가 그 결과다.
다음 장에서는 속성을 어느 엔터티에 둘지 판단하는 기준인 함수 종속과 정규화를 다룬다.