5장 데이터 무결성
데이터 무결성은 DB에 들어 있는 데이터가 정확하고, 서로 모순되지 않는 상태를 말한다. 이번 장에서는 그 상태를 앱 코드에만 맡기지 않고 DB의 제약 조건으로 지키는 방법을 정리한다. 강의 섹션 10에 해당한다. MySQL의 제약 조건 처리
1. 무결성이 중요한 이유
쇼핑몰 데이터에서 다음과 같은 일이 생겼다고 하자.
- 가격이 -5000원인 상품.
- 같은 이메일로 가입한 회원 두 명.
- 이미 지운 회원 번호를 가리키는 주문.
- 주문 상태가 비어 있는 주문.
이런 데이터는 화면 오류, 잘못된 정산, 고객 문의로 이어진다. 앱의 검증 코드로 막을 수도 있지만 앱 검증만으로는 모든 경로를 막기 어렵다. 관리자 도구, 배치, 다른 서비스, 사람이 직접 실행한 SQL도 같은 테이블을 고친다. 검증 코드에 버그가 있거나, 두 요청이 동시에 들어와 둘 다 "이메일 중복 없음"을 확인하고 저장하는 경우도 있다.
DB의 제약 조건은 데이터가 어느 경로로 들어오든 마지막에 한 번 더 막는다. 앱 검증은 사용자에게 친절한 오류를 보여 주는 용도로 두고, 절대 깨지면 안 되는 규칙은 DB에도 건다. InnoDB 같은 트랜잭션 저장 엔진은 제약 위반이 나면 그 문장을 자동으로 롤백한다. 기본키와 UNIQUE 제약
2. 기본 제약 조건
CREATE TABLE member (
member_id BIGINT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(100) NOT NULL UNIQUE,
name VARCHAR(50) NOT NULL,
grade VARCHAR(10) NOT NULL DEFAULT 'BASIC',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);| 제약 | 막는 것 | 함께 생기는 것 |
|---|---|---|
| NOT NULL | 값이 없는 행 | 없음 |
| UNIQUE | 같은 값이 두 번 들어가는 것 | 인덱스 |
| PRIMARY KEY | 중복과 NULL 모두 | 인덱스(InnoDB에서는 행의 저장 순서) |
| DEFAULT | INSERT에서 빠진 값 | 없음 |
NOT NULL
NOT NULL은 그 컬럼에 반드시 값이 있어야 한다는 규칙이다. NULL은 "모름"을 뜻해서 비교 결과도 참·거짓이 아닌 "모름"이 되고, 집계와 조건문에서 자주 실수를 부른다. 값이 항상 있어야 하는 컬럼은 처음부터 NOT NULL로 만든다.
UNIQUE
UNIQUE는 컬럼(또는 컬럼 묶음)의 값이 테이블 안에서 하나뿐이도록 한다. 같은 값을 넣으면 오류가 난다. 앞의 "동시에 같은 이메일로 가입" 문제는 앱의 중복 확인으로는 막기 어렵지만 UNIQUE 제약은 막는다. CREATE TABLE
UNIQUE 컬럼이 NULL을 허용하면 NULL은 여러 개 들어갈 수 있다. NULL끼리는 같은 값으로 보지 않기 때문이다. "값이 있다면 중복 불가"인지 "반드시 있고 중복 불가"인지에 따라 NOT NULL을 함께 건다. CREATE TABLE
PRIMARY KEY
기본키는 각 행을 하나로 식별하는 값이고, UNIQUE와 NOT NULL을 합친 것과 같다. 기본키 컬럼에 NOT NULL을 쓰지 않아도 MySQL이 알아서 NOT NULL로 만든다. 테이블당 하나만 둘 수 있고 이름은 항상 PRIMARY다. CREATE TABLE
기본키는 비즈니스 의미가 없는 값(자동 증가 번호 등)으로 두는 경우가 많다. 이메일이나 주민번호처럼 의미 있는 값은 바뀔 수 있고, 바뀌면 그 값을 참조하는 모든 테이블을 함께 고쳐야 하기 때문이다. InnoDB에서 기본키가 저장 순서를 정하는 구조는 성능 최적화 강의의 섹션 8에서 다룬다.
DEFAULT
DEFAULT는 INSERT에서 값을 빼먹었을 때 넣을 값을 정한다. 상수뿐 아니라 괄호로 감싼 식도 쓸 수 있다. CURRENT_TIMESTAMP는 괄호 없이 쓸 수 있는 예외다. 데이터 타입 기본값
엄격 모드
MySQL 8.4의 기본 SQL 모드에는 STRICT_TRANS_TABLES가 들어 있다. 엄격 모드에서는 타입이 맞지 않거나 범위를 벗어난 값을 넣으면 오류를 낸다. 엄격 모드가 꺼져 있으면 MySQL이 값을 비슷한 값으로 바꿔 넣고 경고만 남기므로, 잘못된 데이터가 조용히 들어간다. 운영 DB의 sql_mode가 기본값에서 바뀌어 있지 않은지 확인한다. 서버 SQL 모드
SELECT @@sql_mode;3. 외래 키 제약 조건
외래 키(foreign key)는 자식 테이블의 값이 부모 테이블에 실제로 있는 값만 가리키도록 한다. 주문(자식)의 member_id는 회원(부모)에 있는 회원 번호여야 한다. FOREIGN KEY 제약
CREATE TABLE orders (
order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
member_id BIGINT NOT NULL,
status VARCHAR(20) NOT NULL,
ordered_at DATETIME NOT NULL,
CONSTRAINT fk_orders_member
FOREIGN KEY (member_id) REFERENCES member (member_id)
);
INSERT INTO orders (member_id, status, ordered_at) VALUES (999999, 'ORDERED', NOW());
-- 오류: 999999번 회원이 없다외래 키는 두 방향에서 데이터를 지킨다. FOREIGN KEY 제약: 참조 동작
- 자식 쪽: 부모에 없는 값을 자식에 넣거나, 그런 값으로 바꾸는 것을 거부한다.
- 부모 쪽: 자식이 가리키고 있는 부모 행을 지우거나 키를 바꾸려 할 때, 정해 둔 참조 동작(referential action)대로 처리한다.
참조 동작
ON DELETE, ON UPDATE 뒤에 참조 동작을 쓴다. 아무것도 쓰지 않으면 NO ACTION이고, InnoDB에서 이것은 RESTRICT와 같다. 자식이 있는 부모 행은 지울 수 없다는 뜻이다. FOREIGN KEY 제약: 참조 동작
RESTRICT,NO ACTION: 자식이 있으면 부모의 삭제·변경을 거부한다.CASCADE: 부모를 지우거나 바꾸면 자식도 함께 지우거나 바꾼다.SET NULL: 부모를 지우거나 바꾸면 자식의 외래 키 컬럼을 NULL로 만든다. 그 컬럼이 NOT NULL이면 쓸 수 없다.SET DEFAULT: 문법은 인식하지만 InnoDB는 이 동작이 들어간 테이블 정의를 거부한다.
ON DELETE CASCADE는 편리하지만 위험하다. 회원 한 명을 지웠는데 주문, 결제, 리뷰가 연쇄로 사라질 수 있다. 연쇄로 지워진 행은 트리거도 실행하지 않는다. 주문처럼 남겨야 하는 기록은 기본값(RESTRICT)으로 두고, 회원 탈퇴는 삭제 대신 상태값으로 처리하는 경우가 많다.
외래 키와 인덱스
MySQL은 외래 키 검사가 풀 테이블 스캔이 되지 않도록 외래 키 컬럼에 인덱스를 요구하고, 없으면 자동으로 만든다. 부모 쪽 참조 컬럼은 MySQL 8.4의 기본 설정에서 기본키나 UNIQUE 키여야 한다. 중복을 허용하는 인덱스를 참조하던 예전 방식은 폐기 예정이다. FOREIGN KEY 제약: 조건과 제약, restrict_fk_on_non_standard_key
외래 키를 쓸지에 대한 판단
실무에서는 외래 키를 걸지 않는 팀도 많다. 따져 볼 점은 다음과 같다.
- 얻는 것: 잘못된 참조가 생길 수 없다는 보장. 데이터가 어느 경로로 들어와도 지켜진다.
- 내는 것: 자식을 넣거나 고칠 때마다 부모를 확인하는 비용. 이 확인은 부모 행에 공유 잠금을 걸기 때문에 같은 부모 행을 고치는 다른 트랜잭션과 서로 기다릴 수 있다. InnoDB의 잠금 설정
- 어려운 점: 테이블을 다른 DB로 나누면 DB 사이에는 외래 키를 걸 수 없다. 대량 데이터 이관 때 순서를 맞춰야 한다.
외래 키를 쓰지 않기로 했다면 그 무결성을 누가 지키는지(앱의 어느 코드, 정기 점검 쿼리)를 정해 둔다.
4. CHECK 제약 조건
CHECK 제약은 행이 만족해야 하는 조건식을 정하고, 조건이 거짓이 되는 INSERT와 UPDATE를 거부한다. CHECK 제약
CREATE TABLE product (
product_id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price INT NOT NULL,
discount_price INT NULL,
stock_quantity INT NOT NULL,
CONSTRAINT chk_product_price CHECK (price >= 0),
CONSTRAINT chk_product_stock CHECK (stock_quantity >= 0),
CONSTRAINT chk_product_discount CHECK (discount_price <= price)
);
UPDATE product SET stock_quantity = stock_quantity - 3 WHERE product_id = 1;
-- 재고가 2개뿐이었다면 오류: chk_product_stock 위반마지막 줄의 예처럼 CHECK 제약은 재고가 음수가 되는 것을 DB에서 막는 마지막 안전장치가 된다. 동시에 주문이 몰리는 상황에서 재고를 정확히 차감하는 방법은 트랜잭션과 잠금 문제이므로 6장과 트랜잭션과 데이터 정합성에서 이어서 본다.
규칙은 다음과 같다. CHECK 제약
- 컬럼 하나에 거는 컬럼 제약과, 여러 컬럼을 함께 보는 테이블 제약으로 쓸 수 있다. 위의
discount_price <= price는 테이블 제약이다. - 조건이 NULL(모름)이 되면 위반이 아니다.
discount_price가 NULL인 행은discount_price <= price가 NULL이므로 통과한다. - 다른 테이블의 컬럼, 서브쿼리,
NOW()같은 실행할 때마다 값이 바뀌는 함수,AUTO_INCREMENT컬럼은 쓸 수 없다. - 이름을 주지 않으면
product_chk_1처럼 자동으로 붙는다. 오류 메시지에 이름이 나오므로 직접 붙이는 편이 원인을 찾기 쉽다.
CHECK 제약은 MySQL 8.0.16부터 실제로 검사한다. 그 전 버전은 CHECK 문법을 받아들이기만 하고 무시했다. 오래된 자료나 오래된 버전의 DB에서는 CHECK를 걸어도 막히지 않았다는 점을 기억한다. MySQL 8.0 CHECK 제약
이해를 확인할 질문
- 앱에서 이메일 중복을 확인하고 저장하는데도 중복 회원이 생길 수 있는 이유는 무엇인가?
- UNIQUE 컬럼에 NULL이 여러 개 들어갈 수 있는 이유는 무엇인가?
- 외래 키에 참조 동작을 쓰지 않으면 부모 행을 지울 때 어떻게 되는가?
- 주문 테이블에
ON DELETE CASCADE를 걸면 어떤 위험이 있는가? discount_price가 NULL인 행은CHECK (discount_price <= price)를 통과하는가?