2장 : 관계형 데이터 모델
무결성 제약
데이터베이스에 잘못된 데이터가 들어가면, 그 위에서 동작하는 모든 애플리케이션이 잘못된 결과를 냅니다. 무결성 제약(Integrity Constraint)은 데이터의 정확성과 일관성을 보장하기 위한 규칙입니다.
무결성 제약은 DBMS가 자동으로 강제하는 규칙입니다. 애플리케이션 코드에서 검증하는 것보다 훨씬 안전합니다. 코드에서는 검증을 빠뜨릴 수 있지만, DBMS는 어떤 경우에도 제약 조건을 통과하지 못하는 데이터를 거부합니다.
┌────────────────────────────────────────────────────────┐
│ 상황: 학생 테이블에 학번이 중복 저장되었다면? │
│ │
│ * 성적 조회 시 다른 학생의 성적이 표시될 수 있음 │
│ * 장학금 지급 시 잘못된 학생에게 지급될 수 있음 │
│ * 수강 신청이 엉뚱한 학생에게 등록될 수 있음 │
│ │
│ → 데이터 입력 시점에서 차단하는 것이 최선 │
│ → 이것이 무결성 제약의 역할 │
└────────────────────────────────────────────────────────┘무결성 제약의 분류
무결성 제약은 크게 5가지로 분류됩니다.
┌─────────────────────────────────────────────────────┐
│ 1. 개체 무결성 (Entity Integrity) │
│ → 기본키는 NULL 불가, 중복 불가 │
│ │
│ 2. 참조 무결성 (Referential Integrity) │
│ → 외래키는 참조 테이블에 존재하는 값만 가능 │
│ │
│ 3. 도메인 무결성 (Domain Integrity) │
│ → 속성 값은 정의된 범위 내의 값만 가능 │
│ │
│ 4. 키 무결성 (Key Integrity) │
│ → 릴레이션에는 반드시 키가 존재해야 함 │
│ │
│ 5. NULL 무결성 │
│ → 특정 속성에 NULL이 올 수 없음을 보장 │
└─────────────────────────────────────────────────────┘개체 무결성 (Entity Integrity)
기본키는 NULL이 될 수 없고, 중복될 수 없습니다. 기본키가 NULL이면 해당 튜플을 식별할 수 없고, 중복이면 어떤 튜플인지 구분할 수 없기 때문입니다.
CREATE TABLE students (
id NUMBER PRIMARY KEY, -- NULL 불가, 중복 불가
name VARCHAR2(50) NOT NULL
);
INSERT INTO students VALUES (NULL, '김철수'); -- 에러! PK에 NULL
INSERT INTO students VALUES (1, '김철수');
INSERT INTO students VALUES (1, '이영희'); -- 에러! PK 중복복합 기본키에서의 개체 무결성
복합 기본키(두 개 이상의 컬럼으로 구성된 기본키)에서는 각 개별 컬럼에 NULL이 올 수 없습니다.
CREATE TABLE enrollments (
student_id NUMBER,
course_id NUMBER,
grade CHAR(2),
PRIMARY KEY (student_id, course_id) -- 복합 PK
);
-- 복합 PK의 어느 한 컬럼이라도 NULL이면 오류
INSERT INTO enrollments VALUES (NULL, 101, 'A'); -- 에러!
INSERT INTO enrollments VALUES (1, NULL, 'A'); -- 에러!
-- 조합이 같으면 중복 오류
INSERT INTO enrollments VALUES (1, 101, 'A');
INSERT INTO enrollments VALUES (1, 101, 'B'); -- 에러! (1, 101) 중복기본키 자동 생성
대부분의 DBMS는 기본키 값을 자동으로 생성하는 기능을 제공합니다.
-- MySQL: AUTO_INCREMENT
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
-- PostgreSQL: SERIAL / IDENTITY
CREATE TABLE users (
id SERIAL PRIMARY KEY, -- 또는: id INT GENERATED ALWAYS AS IDENTITY
name VARCHAR(50) NOT NULL
);
-- Oracle: SEQUENCE + TRIGGER (또는 12c부터 IDENTITY)
CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1;
CREATE TABLE users (
id NUMBER DEFAULT user_seq.NEXTVAL PRIMARY KEY,
name VARCHAR2(50) NOT NULL
);자동 증가 PK를 사용하면 개체 무결성이 DBMS에 의해 자동으로 보장됩니다.
참조 무결성 (Referential Integrity)
외래키 값은 참조하는 테이블의 기본키에 존재하는 값이거나, NULL이어야 합니다. 이 규칙은 테이블 간의 관계를 정확하게 유지합니다.
students
┌────┬──────┐
│ id │ name │
├────┼──────┤
│ 1 │김철수│
│ 2 │이영희│
└────┴──────┘
enrollments
┌────────────┬───────────┬───────┐
│ student_id │ course_id │ grade │
├────────────┼───────────┼───────┤
│ 1 │ 101 │ A │
│ 3 │ 102 │ B │
└────────────┴───────────┴───────┘
↑
student_id = 3 인 학생이 students에 없음
→ 참조 무결성 위반참조 무결성이 위반될 수 있는 4가지 연산
┌──────────────────────────────────────────────────────┐
│ 1. 자식 테이블에 INSERT │
│ → 부모에 없는 FK 값 삽입 시 위반 │
│ 예: orders에 customer_id = 999 삽입 │
│ (customers에 id = 999가 없음) │
├──────────────────────────────────────────────────────┤
│ 2. 자식 테이블의 FK UPDATE │
│ → 부모에 없는 값으로 변경 시 위반 │
│ 예: orders의 customer_id를 999로 변경 │
├──────────────────────────────────────────────────────┤
│ 3. 부모 테이블에서 DELETE │
│ → 자식에서 참조 중인 행 삭제 시 위반 │
│ 예: customers에서 id = 1 삭제 │
│ (orders에 customer_id = 1인 행이 있음) │
├──────────────────────────────────────────────────────┤
│ 4. 부모 테이블의 PK UPDATE │
│ → 자식에서 참조 중인 값을 변경하면 위반 │
│ 예: customers의 id를 1 → 100 변경 │
│ (orders의 customer_id = 1이 고아가 됨) │
└──────────────────────────────────────────────────────┘참조 동작 옵션
참조 중인 데이터를 삭제하거나 수정할 때, DBMS가 어떻게 처리할지를 지정할 수 있습니다.
| 옵션 | 동작 | 사용 예 |
|---|---|---|
| RESTRICT | 삭제/수정 거부 (기본값) | 주문이 있는 고객 삭제 방지 |
| CASCADE | 참조 행도 함께 삭제/수정 | 게시글 삭제 시 댓글도 삭제 |
| SET NULL | 외래키를 NULL로 설정 | 담당자 삭제 시 업무의 담당자를 비움 |
| SET DEFAULT | 외래키를 기본값으로 설정 | 카테고리 삭제 시 미분류로 변경 |
| NO ACTION | RESTRICT과 유사 (트랜잭션 끝에 검사) | SQL 표준 동작 |
CREATE TABLE orders (
id NUMBER PRIMARY KEY,
customer_id NUMBER REFERENCES customers(id) ON DELETE CASCADE,
product_id NUMBER REFERENCES products(id) ON DELETE SET NULL
);각 옵션의 동작 시나리오
부모: customers (id=1, name='김철수')
자식: orders (id=101, customer_id=1, amount=50000)
DELETE FROM customers WHERE id = 1;
RESTRICT
→ 에러! orders에서 참조 중이므로 삭제 거부
CASCADE
→ customers에서 id=1 삭제
→ orders에서 customer_id=1인 행도 함께 삭제
→ orders 테이블: (빈 테이블)
SET NULL
→ customers에서 id=1 삭제
→ orders의 customer_id를 NULL로 변경
→ orders: (id=101, customer_id=NULL, amount=50000)
SET DEFAULT
→ customers에서 id=1 삭제
→ orders의 customer_id를 기본값(예: 0)으로 변경
→ orders: (id=101, customer_id=0, amount=50000)실무에서의 CASCADE 사용 주의
CASCADE는 편리하지만 위험할 수 있습니다. 부모 행 하나를 삭제했는데 연쇄적으로 수천 개의 행이 삭제될 수 있습니다.
users → orders → order_items → order_item_options
↓
payments → payment_details
users에서 1명 삭제 시 CASCADE가 걸려 있다면:
→ orders 10건 삭제
→ order_items 50건 삭제
→ order_item_options 200건 삭제
→ payments 10건 삭제
→ payment_details 30건 삭제
총 300건 연쇄 삭제!실무에서는 CASCADE 대신 소프트 삭제(Soft Delete) 방식을 많이 사용합니다.
-- 물리적 삭제 대신 deleted_at 컬럼으로 논리적 삭제
ALTER TABLE users ADD deleted_at TIMESTAMP NULL;
-- 삭제 시
UPDATE users SET deleted_at = CURRENT_TIMESTAMP WHERE id = 1;
-- 조회 시
SELECT * FROM users WHERE deleted_at IS NULL;도메인 무결성 (Domain Integrity)
속성 값은 정의된 도메인에 속해야 합니다. 나이에 음수가 들어가거나, 성별에 XYZ가 들어가면 안 됩니다.
CHECK 제약조건
CREATE TABLE employees (
id NUMBER PRIMARY KEY,
name VARCHAR2(100) NOT NULL,
age NUMBER CHECK (age >= 0 AND age <= 150),
gender CHAR(1) CHECK (gender IN ('M', 'F')),
email VARCHAR2(200) UNIQUE,
salary NUMBER CHECK (salary >= 0),
hire_date DATE CHECK (hire_date <= SYSDATE)
);
INSERT INTO employees VALUES (1, '김철수', -5, 'M', 'a@t.co', 5000, SYSDATE);
-- 에러! CHECK 위반: age >= 0
INSERT INTO employees VALUES (1, '김철수', 25, 'X', 'a@t.co', 5000, SYSDATE);
-- 에러! CHECK 위반: gender IN ('M', 'F')NOT NULL 제약조건
CREATE TABLE products (
id NUMBER PRIMARY KEY,
name VARCHAR2(100) NOT NULL, -- 상품명은 반드시 필요
price NUMBER NOT NULL, -- 가격도 반드시 필요
description VARCHAR2(500) -- 설명은 선택 (NULL 허용)
);UNIQUE 제약조건
UNIQUE는 해당 컬럼의 값이 테이블 전체에서 유일해야 합니다. PRIMARY KEY와 달리 NULL을 허용합니다.
CREATE TABLE users (
id NUMBER PRIMARY KEY,
email VARCHAR2(200) UNIQUE, -- 이메일은 유일해야
phone VARCHAR2(20) UNIQUE, -- 전화번호도 유일해야
nickname VARCHAR2(50) UNIQUE -- 닉네임도 유일해야
);
-- NULL은 중복으로 취급하지 않음 (대부분의 DBMS)
INSERT INTO users VALUES (1, NULL, NULL, '철수');
INSERT INTO users VALUES (2, NULL, NULL, '영희'); -- OK (NULL은 중복이 아님)DEFAULT 제약조건
CREATE TABLE posts (
id NUMBER PRIMARY KEY,
title VARCHAR2(200) NOT NULL,
status VARCHAR2(20) DEFAULT 'draft', -- 기본값: 초안
views NUMBER DEFAULT 0, -- 기본값: 0
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO posts (id, title) VALUES (1, '첫 게시물');
-- status = 'draft', views = 0, created_at = 현재시간 (자동 설정)사용자 정의 무결성 (비즈니스 규칙)
위 세 가지 외에도 비즈니스 로직에 따른 규칙을 정의할 수 있습니다. CHECK 제약조건이나 트리거(Trigger)를 사용합니다.
-- 1. 시작일은 종료일보다 이전이어야 함
CREATE TABLE projects (
id NUMBER PRIMARY KEY,
name VARCHAR2(100),
start_date DATE,
end_date DATE,
CHECK (start_date <= end_date)
);
-- 2. 할인율은 0% ~ 50% 사이
CREATE TABLE promotions (
id NUMBER PRIMARY KEY,
discount_rate NUMBER CHECK (discount_rate >= 0 AND discount_rate <= 50)
);트리거를 사용한 복잡한 규칙
CHECK로 표현할 수 없는 복잡한 규칙은 트리거(Trigger)로 구현합니다.
-- 재고보다 많은 수량 주문 방지
CREATE OR REPLACE FUNCTION check_stock()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.quantity > (SELECT stock FROM products WHERE id = NEW.product_id) THEN
RAISE EXCEPTION '재고 부족: 요청 수량(%)이 재고(%)를 초과',
NEW.quantity,
(SELECT stock FROM products WHERE id = NEW.product_id);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_check_stock
BEFORE INSERT ON order_items
FOR EACH ROW
EXECUTE FUNCTION check_stock();무결성 제약과 성능
무결성 제약은 데이터 품질을 보장하지만 성능에 영향을 줍니다.
┌────────────────────────────────────────────────────────┐
│ PRIMARY KEY │
│ → Unique Index 자동 생성 → INSERT 시 인덱스 갱신 비용 │
│ → 하지만 검색 성능은 향상 │
├────────────────────────────────────────────────────────┤
│ FOREIGN KEY │
│ → INSERT/UPDATE 시 부모 테이블 조회 필요 │
│ → DELETE 시 자식 테이블 조회 필요 │
│ → 대량 INSERT 시 성능 저하 가능 │
├────────────────────────────────────────────────────────┤
│ CHECK │
│ → 단순 조건은 성능 영향 미미 │
│ → 복잡한 조건이면 약간의 오버헤드 │
├────────────────────────────────────────────────────────┤
│ UNIQUE │
│ → Unique Index 생성 → PK와 유사한 비용 │
└────────────────────────────────────────────────────────┘
실무 팁:
* 대량 데이터 적재(ETL) 시 제약 조건을 일시적으로 비활성화
* 적재 후 제약 조건을 다시 활성화하며 검증-- Oracle
ALTER TABLE orders DISABLE CONSTRAINT fk_customer;
-- 대량 INSERT 실행
ALTER TABLE orders ENABLE CONSTRAINT fk_customer;
-- PostgreSQL
ALTER TABLE orders DISABLE TRIGGER ALL;
-- 대량 INSERT 실행
ALTER TABLE orders ENABLE TRIGGER ALL;무결성 제약 확인과 관리
-- Oracle: 테이블의 제약 조건 목록
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';
-- PostgreSQL: 제약 조건 목록
SELECT conname, contype, consrc
FROM pg_constraint
WHERE conrelid = 'employees'::regclass;
-- MySQL: 제약 조건 목록
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'employees';-- 제약 조건 추가
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary >= 0);
ALTER TABLE orders ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);
-- 제약 조건 삭제
ALTER TABLE employees DROP CONSTRAINT chk_salary;
ALTER TABLE orders DROP CONSTRAINT fk_customer;
-- 제약 조건 이름 변경
ALTER TABLE employees RENAME CONSTRAINT chk_salary TO chk_emp_salary;무결성 제약과 애플리케이션의 역할
무결성 검증을 어디에서 해야 하는지는 중요한 설계 결정입니다.
데이터 품질은 모든 시스템의 기반이므로, DB 레벨에서 반드시 제약 조건을 설정하고 애플리케이션에서도 보조적으로 검증해야 합니다.
┌──────────────────────────────────────────────────────┐
│ DBMS 제약 조건 (1차 방어선, 반드시 필요) │
│ 장점: 절대적, 어떤 경로로든 무결성 보장 │
│ 단점: 세밀한 에러 메시지 제공 어려움 │
├──────────────────────────────────────────────────────┤
│ 애플리케이션 서버 (2차 검증) │
│ 장점: 비즈니스 규칙 표현 유연, 친절한 에러 메시지 │
│ 단점: 우회 가능 (다른 클라이언트, DB 직접 접속) │
├──────────────────────────────────────────────────────┤
│ 프론트엔드 (사용자 경험용) │
│ 장점: 즉각적 피드백, UX 향상 │
│ 단점: 쉽게 우회 가능, 절대 신뢰하면 안 됨 │
└──────────────────────────────────────────────────────┘
권장: 3중 검증 (프론트 + 서버 + DB)
최소: DB 제약 조건은 반드시 설정무결성 제약 정리
| 무결성 유형 | 대상 | 보장하는 것 | 구현 방법 |
|---|---|---|---|
| 개체 무결성 | 기본키 | 튜플의 유일 식별 | PRIMARY KEY |
| 참조 무결성 | 외래키 | 테이블 간 관계 정합성 | FOREIGN KEY |
| 도메인 무결성 | 속성 값 | 유효한 값만 저장 | CHECK, NOT NULL, UNIQUE, DEFAULT |
| 키 무결성 | 릴레이션 | 키의 존재 보장 | PRIMARY KEY, UNIQUE |
| 사용자 정의 | 비즈니스 규칙 | 업무 규칙 준수 | CHECK, TRIGGER |
핵심 정리
┌────────────────────────────────────────────────────────┐
│ 무결성 제약 = DBMS가 강제하는 데이터 품질 규칙 │
├────────────────────────────────────────────────────────┤
│ 개체 무결성: PK ≠ NULL, PK ≠ 중복 │
│ → PRIMARY KEY로 구현 │
│ → 복합 PK에서도 모든 컬럼 NOT NULL │
├────────────────────────────────────────────────────────┤
│ 참조 무결성: FK → 부모의 PK에 존재하는 값 또는 NULL │
│ → RESTRICT: 삭제 거부 │
│ → CASCADE: 함께 삭제 │
│ → SET NULL: FK를 NULL로 │
│ → SET DEFAULT: FK를 기본값으로 │
├────────────────────────────────────────────────────────┤
│ 도메인 무결성: 정의된 범위 내의 값만 허용 │
│ → CHECK: 조건식 │
│ → NOT NULL: NULL 불가 │
│ → UNIQUE: 중복 불가 (NULL 허용) │
│ → DEFAULT: 기본값 설정 │
├────────────────────────────────────────────────────────┤
│ 사용자 정의: 비즈니스 규칙 │
│ → CHECK (단순 규칙) │
│ → TRIGGER (복잡한 규칙) │
├────────────────────────────────────────────────────────┤
│ 성능: 대량 적재 시 제약 비활성화 → 적재 → 재활성화 │
└────────────────────────────────────────────────────────┘다음 절에서는 SQL의 이론적 기반인 관계 대수를 다루겠습니다.