postgreSQL PRIMARY KEY, FOREIGN KEY

PRIMARY KEY (기본 키)

테이블에서 각 행을 유일하게 식별하는 컬럼(또는 컬럼들의 조합)

이 행은 다른 어떤 행과도 절대 겹치지 않는다 라고 보장해주는 제약 조건

CREATE TABLE users ( 
    id INT PRIMARY KEY, -- 테이블 만들때부터 PK 설정하기
    name VARCHAR(20),
    age INT
);

CREATE TABLE users ( 
    id INT,
    name VARCHAR(20),
    age INT,
    PRIMARY KEY(id, name) -- 복합 PK
);

ALTER TABLE table_name ADD PRIMARY KEY (column_name); -- 테이블을 만든 뒤에 PK설정하기
ALTER TABLE table_name ADD PRIMARY KEY (column1, column2); -- 복합 PK

PRIMARY KEY로 설정된 값의 특징

• 중복 불가
• NULL 불가
• 테이블당 1컬럼만 설정 가능
• 대표 식별자 역할
• 자동으로 INDEX 생성함

UNIQUE와 다른점

• 유니크는 NULL 가능
• 유니크는 테이블당 여러 컬럼이 적용 가능
• 유니크 컬럼은 테이블의 대표 식별자는 아니고 그냥 컬럼에 해당값이 유일해야 한다는 제약

사후로 PK를 설정하는 경우 NULL값이 있거나 중복값이 있거나 이미 테이블에 PK가 있다면 에러가 난다.

복합키를 사용할때 INDEX를 쓰려면 Leftmost Prefix Rule에 유의해야한다.

대부분은 아래와 같이 SERIAL과 함께 쓰인다. 정렬이 빠르고 인덱스 효율이 좋고 FK연결이 쉽기 때문.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
);

---

FOREIGN KEY (외래 키)

한 테이블의 컬럼이 다른 테이블의 컬럼을 참조하도록 강제하는 제약조건

"이 값은 반드시 부모 테이블에 존재해야 한다" 라는 뜻이다.

 

예를 들어 유저와 캐릭터의 관계를 생각해보자. 유저 계정에 캐릭터목록이 있는 것이니 어떤 캐릭터 정보에 적힌 유저id가 유저 테이블에 존재하지 않는다면 그건 말이 안되는 일이다(이런 경우를 고아 데이터 라고 한다). 이 때 유저 테이블의 user_id에 캐릭터 테이블의 user_id를 종속시켜서 말이 안되는 일을 방지하는 것.

create table users (
	user_id SERIAL primary key,
	name varchar(20) not null
);

CREATE TABLE game.characters (
    char_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES auth.users(user_id),
    nickname VARCHAR(12) UNIQUE NOT NULL
);

좌 users 테이블, 우 characters 테이블

FK가 보장하는 것

• 존재하지 않는 부모 참조 금지
• 부모 삭제 시 관계 깨지는것 방지
• 데이터 무결성 유지

FOREIGN KEY로 설정된 값의 특징

• 참조 대상은 PK또는 UNIQUE 컬럼
• 부모 삭제/수정 시 동작 옵션 존재
• INSERT / DELETE / UPDATE 시 자동 검사
• 자동으로 INDEX가 생성되지는 않음 (postgreSQL)

부모값에 종속된 테이블이기 때문에 자식값이 남아있다면 기본적으로 부모값을 제거할 수 없다.

하지만 ON DELETE 설정을 통해 어떻게 대응할지 정해줄 수 있다.

• RESTRICT - 부모 삭제 불가능 (기본값)
• CASCADE - 부모 삭제시 같이 삭제 (부모로부터 FK가져온 행이 전부 삭제) 
• SET NULL - 부모 삭제시 KF를 NULL로
• SET DEFAULT - 부모 삭제시 KF를 기본값으로 변경

create table users (
	user_id SERIAL primary key,
	name varchar(20) not null
);

CREATE TABLE game.characters (
    char_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES auth.users(user_id) ON DELETE CASCADE,
    nickname VARCHAR(12) UNIQUE NOT NULL
);

 

UPDATE의 경우에도 ON UPDATE 설정을 통해 부모값이 UPDATE 됐을 때 어떻게 대응할지 정할 수 있다.

• RESTRICT - 부모값 변경 불가능
• CASCADE - 자식 값도 같이 변경
• SET NULL - 자식 FK를 NULL로
• SET DEFAULT - 기본값으로 변경

대부분 경우 PK는 변경할 일이 없다고 한다. 실무에서는 id값은 절대 안바뀌는 값이라고 생각한다고...