4.1 목표:PK관례 확립

목표는 모든 테이블이 PK를 갖도록하는 것이지만, PK의 본질을 혼동하면 안티패턴을 초래할수 있음

• 데이터베이스 걸계를 접했던 사람이라면 모두 PK가 중요하고 꼭 필요한 테이블의 일부라는 사실일 알고 있음
  • PK는 테이블내의 모든행이 유일함을 보장하기 때문에, 각 행에 접근하는 논리적 매커니즘이 되고 중복행이 저장되는것을 방지함
  • PK는 관계를 생성할 때 FK로 부터 참조되기도 함

까다로운 부분은 PK로 사용할 컬럼을 선정하는 일임

• 대부분의 테이블에서 어느 속성의 값이든 하나 이상의 행에서 나타날 잠재적 가능이 있음
  • 예제로 이름도 분명 중복될수있음
  • 심지어 이메일주소나 미국의 사회보장번호, 납세자ID와 같은 관리적 식별번호조차도 엄밀하게 말하면 유일하지 않음

이런 테이블에는 테이블로 모델링한 영역에서는 아무런 의미도 가지지 않는 인위적인 값을 저장할 새로운 칼럼이 필요함
이 칼럼을 PK로 사용하면(만약 이것이 적절하다면), 다른 속성 칼럼에는 중복값이 들어가는 것을 허용하는 반면 특정 행에 유일하게 접근할수있게됨
이러한 형태의 PK를 가상키(pseudokey)또는 대체키(surrogate key)라 함

여러 클라이언트가 동시에 새로운 행을 삽입하는 경우에도 각 행의 가상키값이 유일하게 할당되는 것을 보장하기 위해,
대부분의 DBMS는 트랜잭션 격리범위 밖에서 유일한 정수 값을 생성하는 매커니즘을 제공함

가상키는 유용한 기능이지만, PK를 선언하는 유일한 방법은 아니다.

4.2 안티패턴

책이나 기사, 프로그래밍 프레임워크는 데이터베이스 내 모든 테이블이 다음과 같은 특성을 가지는 PK칼럼을 가지도록 하는 문화적 관례를 만들었음

• PK칼럼 이름은 id다.
• PK칼럼의 데이터 타입은 32비트 도는 64비트 정수다.
• 유일한 값은 자동생성된다

모든 테이블에 id란 이름의 컬럼이있는것은 너무도 흔해져 이게PK와 동의어가 되어 버렸음
SQL을 배우는 프로그래머들은 PK가 항상 다음과 같은 식으로 정의되는 칼럼이라는 잘못된 생각을 갖게 됨

CREATE TABLE Bugs (
  id          SERIAL PRIMARY KEY,
  description VARCHAR(1000),
  -- . . .
);

모든 테이블에 id칼럼을 추가하는 것은, 그 사용을 이상하게 만드는 몇가지 효과를 초래함

중복키 생성

테이블 안의 다른 칼럼이 자연키로 사용될 수 있는 상황에서 조차 단지 통념에 따라 id칼럼을 PK로 정의한것을 봤을 것임
그 다른 컬럼에 UNIQUE제약조건이 설정되어 있는 경우도 있음.

예를들 Bugs테이블에서는 프로젝트 코드를 앞에 붙여 bug_id를 만들수 있을 것임

CREATE TABLE Bugs (
  id          SERIAL PRIMARY KEY,
  bug_id      VARCHAR(10) UNIQUE,
  description VARCHAR(1000),
  -- . . .
);

INSERT INTO Bugs (bug_id, description, ...)
  VALUES ('VIS-078', 'crashes on save', ...);

이예에서 bug_id칼럼은 각 행을 유일하게 식별할 수 있도록 해준다는 면에서 id와 사용목적이 동일함

중복행 허용

복합키는 여러 칼럼을 포함함
복합키가 사용되는 전형적인 예는 BugsProducts와 같은 교차테이블안에서다.
PK는 특정한 bug_id와 product_id값의 조합이 테이블안에서 한 번만 나타난다는 것을 보장해야함
각 값이 다른 쌍으로 여러번 나타날수 있을지라도 말이다.

그러나 id칼럼을 PK로 사용하는 경우에는 유일해야 하는 두 칼럼에 제약조건이 적용되지 않음

CREATE TABLE BugsProducts (
  id          SERIAL PRIMARY KEY,
  bug_id      BIGINT UNSIGNED NOT NULL,
  product_id  BIGINT UNSIGNED NOT NULL,
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

INSERT INTO BugsProducts (bug_id, product_id)
  VALUES (1234, 1), (1234, 1), (1234, 1); -- duplicates are permitted

Bug와 Products를 연결하기위해 이 교차 테이블을 사용할때, 중복 때문에 의도하지 않은 결과가 발생함
중복을 방지하기 위해서는 id뿐만 아니라 다른 두 칼럼에 UNIQUE제약조건을 걸어줘야 함

CREATE TABLE BugsProducts (
  id          SERIAL PRIMARY KEY,
  bug_id      BIGINT UNSIGNED NOT NULL,
  product_id  BIGINT UNSIGNED NOT NULL,
  UNIQUE KEY (bug_id, product_id),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)

그러나 이 투 칼럼에 UNIQUE제약조건을 걸어야 한다면, id칼럼은 불필요한 것임

모호한 키의 의미

id란 이름은 너무 일반적이기 때문에 아무런 의미도 갖기 못함
이는 PK칼럼 이름이 동일한 두 테이블을 조인할 때 특이 문제가됨

원래의 위치 대신 이름만으로 칼럼을 참조한다면 애플리케이션 코드에서 버그의 id와 계정의id를 어떻게 구분할 것인가?
이는 PHP와 같은 동적언어에서는 특히 문제가 됨.
쿼리 결과가 연관배열에 담겨 반환되는데, 쿼리에 칼럼 별명(alias)를 지정하지 않으면 한 칼럼이 다른 칼럼을 덮어서써버리기 때문임

id칼럼의 이름은 쿼리의 으미를 명확하게 하는데도 도움이 되지않음
그러나 칼럼 이름이 bug_id와 account_id로 되어있다면 쿼리 결과를 읽기도 휠씬 쉬울것임

USING사용

SELECT * FROM Bugs AS b JOIN BugsProducts AS bp ON (b.bug_id = bp.bug_id);

SQL은 두 테이블의 조인을 표현하는 좀더 간략한 문법도 지원함
양쪽 테이블에 칼럼이름이 같다면 앞의 쿼리는 다음과 같이 다시 작성할수 있음

SELECT * FROM Bugs JOIN BugsProducts USING (bug_id);

그러나 모든 테이블이 id란 이름의 가상키를 PK로 정의해야 한다면, 종속된 테이블에서의FK칼럼 이름은 참조하는 PK칼럼의 이름과 같을수 없게됨
따라서 약간은 장황한 ON문법을 사용해야함

SELECT * FROM Bugs AS b JOIN BugsProducts AS bp ON (b.id = bp.bug_id);

어려운 복합키

어떤 개발자는 사용하기 어렵다는 이유로 복합키를 거부하며, 키를 비교할때 모든 칼럼을 비교해야함.
복합 PK를 참조하는 FK는 자신도 복합 FK가 되어야하며, 복합키를 하려면 타이핑을 더 해야함

4.3 안티패턴 인식방법

테이블에서 PK칼럼이름으로 id(지나치게 일반적인 이름)가 사용되고 있으면 이 안티패턴의 징후로 볼수 있음
좀더 의미 있는 이름 대신 id를 선호해야할 이유가 없음

다음과 같은 말 또한 이 안티패턴의 증거가 될수 있음

• "이 테이블에는 PK가 없어도 될것 같은데"
  • 이런 말을 하는 개발자는 PK와 가상키 용어의 의미를 혼동하는 것임
  • 모든 테이블은 중복 행을 방지하고 각 행을 유일하게 식별하기 위해 PK제약조건을 가져야함
  • 아마 자연키나 복합키 사용이 필요할것임

• "다대다 연결에서 왜 중복이 발생했지?"
  • 다대다 관계를 위한 교차 테이블에는 FK칼럼을 묶어 PK제약조건을 걸거나 최소한 UNIQUE 제약조건이라도 걸어 주어야함

• "나는 데이터베이스 이론에서 값은 색인 테이블로 옮기고 ID로 참조해야 한다고 하는 걸 읽었어.그러나 그렇게 하고 싶지 않아"
• "내가 원하는 실제값을 얻기 위해 매번 조인을 해야하기 때문이지"
  • 이는 데이테베이스 설계이론에서 말하는 정규화(normalization)에 대한 흔한 오해이며, 정규화와 가상키와 아무런 상관이 없음

4.4 안티패턴 사용이 합당한 경우

• 일부 객체-관계 프레임워크에서는 CoC(Convention over Configuration)을 통해 개발을 단순화함
  • 이런 프레임워크에서는 모든 테이블이 동일한 방식(칼럼이름은 id고 데이터 타입은 정수인 가상키)으로 PK를 정의한다고 가정함
  • 이런 프레임워크를 사용한다면 그 관례를 따르고 싶을 것이며, 그렇게 해야 프레임워크의 다른 워하는 기능을 사용할수 있기 때문임

• 가상키는 지나치키 긴 자연기를 대체하기 위해 사용한다면 적절한 선택이됨.
  • 예를들어, 파일시스템의 파일 속성을 저장하는 테이블에서, 파일경로는 좋은 자연키가 될수 있지만, 이렇게 긴 문자열을 키로하면 인덱스를 만들고 유지하는데 많은 비용이 들것임

4.5 해법: 상황에 맞추기

PK는 제약조건이지 데이터 타입이 아니다.
데이터 타입이 인덱스르 지원하기만 하면, 어느 칼럼도는 칼럼의 묶음에 대해서도 PK를 선언할수있음
도한 테이블의 특정칼럼을 PK로 잡지 않고도 자동증가하는 정수값을 가지도록정의할수 있음
이 두 개념은 서로 독립적인 것임

있는 그대로 말하기

PK에 의미 있는 이름을 선택해야함
이 이름은 PK가 식별하는 엔터티의 타입을 나타내야함
예를들어 Bugs 테이블의 PK는 bug_id가되어야 함

FK에서도 가능하다면 같은 칼럼이름을 사용해야함
이는 종종PK이름이 스키나 매에서 유일해야함을 뜻함
하나가 다른쪽의 FK가 아닌 한, 동일한 PK이름이 다른 테이블에 나오면 안됨
그러나 예외사항으로 연결의 본질을 더 잘 표현하는 경우라면, FK를 자신의 참조하는 PK이름과 다른게하는 것도 괜찮음

CREATE TABLE Bugs (
  -- . . .
  reported_by  BIGINT UNSIGNED NOT NULL,
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
);

메타데이터 명명 규칙을 설명하는 업계 표준이 있음.
ISO/IEC 11179라는 표준인데, 정보기술 시스템에서 메타데이터의 분류체계 관리에 대한 가이드 라인임
다른 말로하면, 테이블 이름과 칼럼이름을 의미있게 짓는 방법임
다른 ISO 표준과 마찬가지로, 이 문서도 매우 난해하지만 "SQL Progra,,omg Style"에서 SQL에 실용적으로 적용하는 방법이 나옴

관례에서 벗어나기

객체-관계 프레임워크는 id란 이름의 가상키가 사용될 것을 기대하지만, 다른 이름을 사용하도록 재설정하는 것도 허용함
다음은 Ruby on Rails에서의 예다.

class Bug < ActiveRecord::Base
  set_primary_key "bug_id"
end

자연키와 복합기포용

유일함을 보장되고, NULL값을 가지는 경우가없고, 행을 식별하는 용도로 사용할수 있는 속성이 테이블에 있다면,
단지 통념을 따르기 위해 가상키를 추가해야한다는 의무감을 느낄필요가 없음

처음에는 자연키로 손색이 없어 보인던 칼럼이 나중에 알고 보니 적법하게 중복을 허용하는 것으로 밝혀질수도 있으며, 이런 경우에는 가상키를 사용할수 있음

복합키가 적절한 경우에는 이를 사용하기바람
BugProducts테이블에 서와 같이 여러 칼럼의 조합으로 행을 가장 잘 식별할수 있다면, 이 칼럼조합을 복합킬로 사용해야함

CREATE TABLE BugsProducts (
  bug_id      BIGINT UNSIGNED NOT NULL,
  product_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (bug_id, product_id),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

INSERT INTO BugsProducts (bug_id, product_id)
  VALUES (1234, 1), (1234, 2), (1234, 3);

INSERT INTO BugsProducts (bug_id, product_id)
  VALUES (1234, 1); -- error: duplicate entry

복합 PK를 참조하는 FK또는 복합키가 되어야 함에 유의하기 바람
종속되는 테이블에 이렇게 칼럼 조합을 중복해야 하는 것은 안 좋아 보이지만, 장점도 있음
중복된 칼럼 값을 얻을때 조인을 안해도 되기때문에 쿼리가단순해짐

문서에 대하여

• 최초작성일 : 2011년 11월 22일
• 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 인사이트(insight) 에서 출간한 'SQL AntiPatterns : 개발자가 알아야 할 25가지 SQL 함정과 해법'를 참고하였습니다.*