postgresql - Table에 Primary key, Foreign key, Composite key 설정하기

Primary key 

CREATE TABLE IF NOT EXISTS product_type_code(

    product_type_id char(8) PRIMARY KEY not null,

    product_type_name char(100),

    created_at timestamp default current_timestamp.

    created_by varchar not null default current_user,

    updated_at timestamp default current_timestamp,

    updated_by varchar default current user,

)

예시 create table 스크립트에서는 trigger 예제를 제외하였다.

updated_at 필드가 current_timestamp로만 찍히는 걸로 보일 수 있으나

row 데이터 update시

updated_at필드가 update 시각으로 변경되는 trigger와

procedure(function)를 추가하면 된다.

Foreign key

CREATE TABLE IF NOT EXISTS product(

    product_id char(8) PRIMARY KEY not null,

    product_type_code char(8) not null,

    product_name char(100),

    ...     

    constraint fk_product_type_code FOREIGN KEY(product_type_code) references                   product_type_code(product_type_id) ON DELETE CASCADE ON UPDATE CASCADE

)

product 테이블의 product_type_code 필드는
product_type_code테이블의 product_type_code 아이디를 참조하고 

product_type_code 테이블의 product_type_id가 삭제되거나 갱신 시,

참조하는 테이블에도 삭제와 갱신이 발생한다.

Composite key

기본키는 테이블에 하나만 존재한다.

기본키는 복수의 컬럼으로 구성될 수 있다.

2개 이상의 필드를 기본키로서 구성할 때 복합키를 사용한다. 

만약 1개의 물품이 다양한 product_type을 가지고 있다면
다음과 같은 매핑 테이블을 구성할 수 있다.

product_type에 따라 테이블을 따로 구성할 수도 있겠다.

가구/가전 타입 테이블

욕실용품 타입 테이블

주방용품 타입 테이블 등

Composite key를 사용하기 위한 매핑 테이블의 구조는 다음과 같다.

CREATE TABLE IF NOT EXISTS product_ type_map(

    product_id char(8)

    product_type_code char(8)

    product_name char(100)

    product_type_name char(100)

    constraint product_type_map_pk primary key(product_id, product_type_code)

);

위와 같이 복합키로서 pk를 설정해야지 Index가 설정되어

빠르게 탐색할 수 있다.

또한, 조회 시 PK가 설정되어있는 값을 WHERE조건에 넣어야지

설정된 Index의 효과를 볼 수 있다.