2021 04 06 - PK를 어떻게 잡아야 하지!

PK

중복행 방지하기

목표: PK 관계 확립

PK는 테이블 내의 모든 행의 유일함을 보장하기 때문에, 각 행에 접근하는 논리적 매커니즘이 된다.(인덱스를 말하는듯?)

중복행이 저장되는것을 방지한다.

만약 PK를 누락하지 않고 테이블을 생성했다고 가정하자.

새로운 컬럼을 추가해서 아무런 의미를 가지지않는 임의의 값을 넣어서 PK처럼 사용한다고 했을때, 이런형태의 컬럼을 가상키, 또는 대체키라고 한다.

안티패턴 : 아무 생각없이 테이블에 id를 PK로 넣지말라구!

pk와 비슷하게 사용되는 키가 만들어질지도 모르니까 말이야.

CREATE TABLE Bugs (
  id          SERIAL PRIMARY KEY,
  bug_id      VARCHAR(10) UNIQUE, --bug_id는 UNIQUE해서 pk처럼 동작하고 있는걸. . ?
  description VARCHAR(1000),
  -- . . .
);

PK가 있는데도 불구하고 중복된 행이 생겨 !

예를들어, BugProducts와 같은 아래의 테이블이 있다고 가정하자.

BugProducts는 Bugs와 Products의 맵핑정보를 저장하는 맵핑테이블이다.

그리고 bug_id와 product_id의 조합은 유일해야 한다!

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)
);

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), --중복된 값이 들어가면 안되니까 유니크 처리해줌. 차라리 얘를 PK로 쓰는게 나은것 같은데?
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

시퀀스의 특별한 범위

예를들어 아래의 쿼리가 있다고 가정하자.

-- 아마 시퀀스를 의도하고 싶었나보다.
SELECT MAX(bug_id) + 1 AS next_bug_id FROM Bugs;

그런데 위의 쿼리는 경쟁상태를 발생시킨다. 그런데 알고보니, 위의 쿼리는 테이블 전체에 락을 걸지 않는다. bug_id라는 컬럼만 건다칸다.

하지만 테이블 잠금은 DB요청을 한줄로 세워 차례로 접근하게 하므로 병목의 원인이 된다.

반면 시퀀스는 트랜잭션 범위 밖에서 동작하므로 여러 클라이언트에 절대 같은 값을 할당하지 않고, 커밋 여부와 상관없이 한번 할당한 값을 되돌리지도 못한다!

대부분의 DBMS는 시퀀스가 생성한 마지막 값을 확인할 수 있는 함수를 제공한다.