[31일차]데이터베이스 무결성 관리, 스키마 개선 작업 결과

데이터베이스 무결성 관리: 중복 키 에러와 외래 키 제약 조건

데이터베이스 설계에서 데이터의 일관성과 정확성을 유지하는 것은 매우 중요하다. 이를 위해 사용되는 UNIQUE 제약 조건과 FOREIGN KEY 제약 조건의 개념, 그리고 이와 관련된 오류 및 명명 규칙에 대해 알아본다.

---

1. Duplicate Key on Write or Update 에러

이 오류는 데이터베이스의 고유성(Uniqueness) 제약 조건을 위반했을 때 발생한다. 주로 MySQL/MariaDB 환경에서 Error Code: 1062. Duplicate entry '...' for key '...'와 같은 형태로 나타나며, 테이블에 새로운 데이터를 삽입(INSERT)하거나 기존 데이터를 수정(UPDATE)할 때, 특정 컬럼에 이미 존재하는 값을 중복해서 입력하려고 시도하면 발생한다.

1.1. 원인: PRIMARY KEY와 UNIQUE KEY

고유성 제약은 주로 두 가지 키를 통해 적용된다.

1. PRIMARY KEY (기본 키): 테이블의 각 행(row)을 고유하게 식별하는 키이다. NULL 값을 허용하지 않으며, 테이블 내에서 절대 중복될 수 없다.
2. UNIQUE KEY (고유 키): PRIMARY KEY와 유사하게 컬럼 내의 모든 값이 고유해야 함을 보장한다. 하지만 PRIMARY KEY와 달리 NULL 값을 허용할 수 있으며(단, NULL 값 자체는 여러 번 중복 저장될 수 있는 경우가 있음), 하나의 테이블에 여러 개의 UNIQUE KEY를 설정할 수 있다.

1.2. 에러 발생 사례 (제공된 스키마 기준)

사례 1: 단일 컬럼 UNIQUE KEY 위반

users 테이블은 email 컬럼에 UNIQUE KEY 제약 조건이 설정되어 있다.

CREATE TABLE `users` (
  ...
  `email` varchar(255) NOT NULL,
  ...
  UNIQUE KEY `email` (`email`)
);

만약 email이 'user@example.com'인 사용자가 이미 존재하는데, 또다시 동일한 이메일로 회원가입을 시도하면 Duplicate entry 'user@example.com' for key 'email' 에러가 발생한다. 이는 데이터베이스가 이메일 중복을 허용하지 않음으로써 데이터 무결성을 지키고 있음을 의미한다.

사례 2: 복합 UNIQUE KEY 위반

carts 테이블은 user_id와 book_id 두 컬럼을 조합한 복합 UNIQUE KEY를 가지고 있다.

CREATE TABLE `carts` (
  ...
  `user_id` int(11) NOT NULL,
  `book_id` int(11) NOT NULL,
  ...
  UNIQUE KEY `user_id` (`user_id`, `book_id`)
);

이는 '한 명의 사용자는 동일한 책을 장바구니에 한 번만 담을 수 있다'는 비즈니스 규칙을 데이터베이스 수준에서 강제하는 것이다. 만약 user_id=1인 사용자가 이미 book_id=10인 책을 장바구니에 담았는데, 또다시 (1, 10) 조합으로 데이터를 INSERT하려고 하면 Duplicate entry '1-10' for key 'user_id' 에러가 발생한다.

---

2. 외래 키(FK) 제약 조건 이름 설정

외래 키(Foreign Key)는 한 테이블의 컬럼이 다른 테이블의 PRIMARY KEY를 참조하도록 하여 두 테이블 간의 관계를 정의하는 역할을 한다. 이때, 각 외래 키 제약 조건에 명확한 이름을 부여하면 유지보수와 디버깅이 매우 용이해진다.

2.1. 이름 설정의 중요성

만약 외래 키 제약 조건에 이름을 직접 지정하지 않으면, 데이터베이스 시스템(MySQL 등)은 orders_ibfk_1, carts_ibfk_2와 같이 자동 생성된, 의미를 알기 어려운 이름을 부여한다. 이렇게 되면 오류 메시지가 발생했을 때 어떤 테이블의 어떤 관계에서 문제가 발생했는지 즉시 파악하기 어렵다.

2.2. 이름 설정 방법: CONSTRAINT 키워드

외래 키 제약 조건의 이름은 CONSTRAINT 키워드를 사용하여 명시적으로 지정할 수 있다.

기본 문법:

CONSTRAINT [제약_조건_이름] FOREIGN KEY ([자식_테이블_컬럼]) REFERENCES [부모_테이블]([부모_테이블_컬럼])

2.3. 이름 설정 사례 (제공된 스키마 기준)

좋은 예: 의미 있는 이름 부여

categories 테이블은 자기 자신을 참조하는 외래 키에 fk_categories_parent라는 명확한 이름을 부여했다.

CREATE TABLE `categories` (
  ...
  `parent_id` int(11) DEFAULT NULL,
  ...
  CONSTRAINT `fk_categories_parent` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
);

• fk: Foreign Key를 의미
• categories: 제약 조건이 속한 테이블 (자식 테이블)
• parent: 참조하는 대상의 역할이나 부모 테이블
  이 이름을 보면 'categories 테이블의 parent_id가 외래 키'임을 쉽게 유추할 수 있다.

개선이 필요한 예: 자동 생성된 이름

orders 테이블의 외래 키 제약 조건 이름은 orders_ibfk_1로 자동 생성되었다.

CREATE TABLE `orders` (
  ...
  `user_id` int(11) NOT NULL,
  ...
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);

이 이름만으로는 user_id가 users 테이블을 참조한다는 사실을 알기 어렵다. 다음과 같이 명명 규칙을 적용하여 개선할 수 있다.

개선된 DDL:

-- 'fk_자식테이블_부모테이블' 명명 규칙 적용
CONSTRAINT `fk_orders_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)

이처럼 일관된 명명 규칙(예: fk_[자식테이블]_[부모테이블])을 적용하면, 스키마의 가독성이 크게 향상되고 오류 발생 시 원인을 신속하게 파악하는 데 큰 도움이 된다.

3. 데이터베이스 스키마 개선 작업 결과

이번 스키마 개선은 데이터 무결성 강화, 성능 최적화, 그리고 유지보수성 향상을 목표로 진행되었다.

---

주요 변경 사항 요약

3-1. 일관성 및 명확성 향상

• 외래 키(FK) 제약 조건 이름 통일: 초기 스키마에서는 ..._ibfk_1과 같이 자동 생성된 이름과 명시적 이름이 혼재했다. 최종 스키마에서는 모든 외래 키 제약 조건의 이름을 fk_[자식테이블]_[부모테이블] 형식으로 통일하여, 제약 조건의 역할을 명확히 알 수 있도록 개선했다.

  • 적용 테이블: books, carts, order_details, reviews, book_details, book_likes, review_likes

• UNIQUE KEY 이름 명확화: reviews 테이블의 (user_id, book_id)에 대한 UNIQUE KEY 이름이 user_id에서 uk_reviews_user_book으로 변경되어, 키의 역할을 명확히 했다.

3-2. 데이터 무결성 강화

• book_details 테이블 isbn 고유성 보장: 도서의 고유 식별자인 isbn 컬럼에 UNIQUE KEY (uk_isbn) 제약 조건을 추가하여, 중복된 ISBN이 입력되는 것을 방지했다.

• reviews 테이블 중복 인덱스 제거: reviews 테이블에 불필요하게 존재하던 중복 인덱스(user_id_2)를 제거하여, 쓰기 성능 저하를 방지하고 스키마 구조를 최적화했다.

3-3. 구조적 개선 및 확장성 확보

• orders 테이블 status 컬럼 변경: ENUM 타입이었던 status 컬럼을 VARCHAR(20) 타입으로 변경했다. 이로 인해 향후 새로운 주문 상태가 추가되더라도 스키마 변경 없이 유연하게 대응할 수 있게 되었다.

• refresh_tokens 테이블 구조 변경:

  • 초기: user_id를 PRIMARY KEY로 사용하여 사용자가 단 하나의 리프레시 토큰만 가질 수 있었다.
  • 최종: id를 PRIMARY KEY로 변경하고 user_id에 일반 인덱스를 추가하여, 한 명의 사용자가 여러 기기에서 로그인하여 다수의 리프레시 토큰을 가질 수 있도록 구조를 개선했다.

3-4. 성능 최적화

• users 테이블 중복 인덱스 제거: email 컬럼에 대한 UNIQUE KEY와 별개로 존재하던 중복 인덱스(email_2)를 제거하여 스키마를 최적화했다.

• book_details 테이블 FULLTEXT 인덱스 추가: description과 같이 긴 텍스트 내용에 대한 검색 성능을 향상시키기 위해 FULLTEXT KEY (idx_description)를 추가했다.

3-5. 데이터 추적 기능 강화

• 감사(Audit) 컬럼 추가: 데이터의 생성 및 수정 이력을 추적하기 위해 categories와 book_details 테이블에 created_at과 updated_at 컬럼을 추가하여, 모든 주요 테이블에서 데이터 변경 이력을 일관되게 관리할 수 있도록 했다.

---

결론

이번 스키마 리팩토링을 통해 초기 설계에 존재했던 잠재적 문제점들을 해결하고, 데이터베이스의 안정성과 확장성을 크게 향상시켰다. 명확한 명명 규칙 적용, 중복 요소 제거, 그리고 구조적 개선을 통해 앞으로의 기능 개발과 유지보수가 더욱 용이해질 것으로 기대된다.