1.3Working with Tables

데이터베이스에서는 테이블끼리 관계를 맺는 경우가 많다. 대표적인 관계의 종류는 다음 네 가지다.

4가지 테이블 관계(Relationships)

• One-to-Many 관계: 하나의 유저가 많은 사진을 가진다.
• Many-to-One 관계: 여러 사진들이 하나의 유저에 속해있다.

이 둘은 관점에 따라 다르게 표현된 것이다. 사진의 입장에서 보면 Many-to-One, 유저의 입장에서 보면 One-to-Many

• One-to-One 관계: 하나의 유저당 하나의 고유 ID를 가진다.(1:1매칭)
• Many-to-Many 관계: 여러 유저가 서로를 팔로우하는 것처럼, 다대다로 연결된다.

Primary Key와 Foreign Key

테이블 간 관계를 연결하기 위해선 Primary Key와 Foreign Key 개념을 이해해야 한다.

Primary Key (기본 키)

• 테이블에서 각 행(row)을 고유하게 식별하기 위한 컬럼이다.
  • 예를 들면, users 테이블의 id

Foreign Key (외래 키)

• 다른 테이블의 Primary Key를 참조하기 위해 존재하는 컬럼이다.
  • 예를 들면, photos 테이블에서 user_id 컬럼은 users 테이블의 id를 참조한다.
    • Primary Key는 보통 id라는 이름을 쓴다.
    • Foreign Key는 xyz_id식으로 이름을 쓴다.
    • 한 테이블에서 Primary Key는 단 하나(또는, 조합)만 존재한다.
    • Foreign Key는 여러 개 존재할 수 있다.
    • Primary Key는 절대 변경되지 않는다.
    • Foreign Key는 관계가 바뀔 경우 수정될 수 있다.

자동으로 생성되는 ID (Auto-Generated ID)

SERIAL 키워드를 통해 1부터 자동으로 2, 3, … 과 같이 등록되는 id를 생성할 수 있다.

sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50)
);

id는 자동 생성되므로, 데이터를 삽입할 때는 username만 입력하면 된다.

sql
INSERT INTO users (username)
VALUES 
	('monahan93'),
  ('pfeffer'),
  ('sim3on');

Foreign Key 컬럼 생성하기

이제, 유저들이 업로드한 사진을 저장할 photos 테이블을 만들어 보자.

sql
CREATE TABLE photos (
  id SERIAL PRIMARY KEY,
  url VARCHAR(200),
  user_id INTEGER REFERENCES users(id)
);

• id: 자동 증가하는 기본 키
• url: 사진 파일의 경로를 저장하는 문자열(길이가 일정하지 않음)
• user_id: 이 사진을 소유한 유저의 ID(users테이블의 id 참조)

이렇게 테이블을 만들면, 데이터 삽입시 user_id를 통해 사진을 누가 가지고 있는지 소유 관계를 표현할 수 있다.

sql
INSERT INTO
  photos (url, user_id)
VALUES
  ('http://one.jpg', 2);

관계를 맺은 테이블에서 데이터 조회하기

테이블 간 관계를 맺으면, 다양한 방식으로 데이터를 함께 조회할 수 있다.

JOIN을 사용한 조회

JOIN 키워드를 사용하면, 두 테이블을 연결해서 필요한 데이터만 골라서 가져올 수 있다.

sql
SELECT url, username FROM photos
JOIN users ON users.id = photos.user_id;

• photos 테이블의 url과 users 테이블의 username을 함께 조회한다.

WHERE로도 연결 가능

물론, 단순한 경우에는 WHERE 절로 Foreign Key 관계를 활용해 데이터 조회를 할 수도 있다. 하지만 여러 테이블을 연결해야 하는 경우에는 JOIN이 더 명확하고 편리하다.

Foreign Key가 만들어내는 제약(Constraints)

Foreign Key를 연결하면 데이터 삽입과 삭제에 제약이 생긴다.

삽입(Insert)시 제약

위의 예시에선,

• photo 테이블에 데이터를 넣을 때, 반드시 존재하는 user_id를 넣어야 한다.

⇒ 이렇게 제약이 생겨야 이상한 data를 넣을 가능성이 줄어들기 때문이다.

• 단, user_id가 NULL을 허용하면, NULL을 넣는 것은 가능하다.

  (이런 경우 나중에 업데이트로 관계를 연결할 수 있다.)

삭제(Delete) 시 제약

Foreign Key를 연결하면, 삭제할 때도 제약이 생긴다.

위의 예시에선,

• users 테이블에서 id = 1인 데이터를 삭제한다면,
• SERIAL로 만든 users 테이블의 id는 다시 재생성되지 않으니
• photos 테이블의 user_id = 1인 데이터들이 소속이 없는 데이터가 되어버린다.

이런 상황에선 아래와 같은 선택지들이 가능하다:

ON DELETE 옵션들

아래는 ON DELETE CASCADE의 예시다.

sql
CREATE TABLE photos (
  id SERIAL PRIMARY KEY,
  url VARCHAR(200),
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);

유저가 삭제되면, 그 유저가 등록한 모든 사진도 자동으로 삭제된다. 포럼에서 메인 포스트를 삭제할 때 댓글도 함께 삭제하는 경우에 자주 사용된다.

아래는 ON DELETE SET NULL의 예시다.

sql
CREATE TABLE photos (
  id SERIAL PRIMARY KEY,
  url VARCHAR(200),
  user_id INTEGER REFERENCES users(id) ON DELETE SET NULL
);