[SQL] 데이터베이스 관계(Relationship) 기본 (1:1, 1:N, N:N 테이블 생성 예시 포함)
필자는 최근 회사에서는 SQL보다는 dynamoDB, redis, mongoDB와 같은 NoSQL 데이터베이스를 주로 활용하다보니, SQL의 기본 개념이 슬슬 가물가물해지기 시작했다(ㅠㅠ). 물론 막상 보면 기억은 나지만, A to Z 설명해보라고 하면 논리정연하게 설명할 자신이 없다보니, 앞으로 간단하게나마 기본 개념들을 시간 날 때마다 정리해보려고 한다.
본 글의 주제는 데이터베이스 관계(Relationship)의 기본 개념이다. 관계와 관련된 모든 키워드를 다루진 않고, 데이터베이스의 관계를 이루는 주요 개념들은 무엇이 있는지, 그리고 각 관계 (1:1, 1:N, N:N) 종류에 따른 설명과 테이블 생성 예시를 다루는 아주 기본적인 내용으로 이루어져있다. 앞으로 꾸준히 여러 키워드들에 대한 in-depth 정리를 할 예정이지만, 특정 키워드들이 잘 이해가 가지 않거나, 대신 정리해줬으면 하는 내용을 적어주면 우선적으로 다뤄보도록 하겠다.
참고로 필자가 첨부하는 그림은 설명도 포함된 경우가 많기 때문에, "그냥 그림이구나~" 하지 말고 한번 글과 함께 자세히 보는 것을 추천한다.
자 시작하자!
Entity(엔티티)란?
데이터베이스 디자인의 시작은 entity를 정의하는데서 시작한다. 여기서 entity란 데이터베이스에 입력될 데이터의 집합을 의미한다. 다시 말해 사람, 장소, 물건, 사건, 개념 등과 같은 명사와 관련된 데이터의 집합인데, 데이터베이스에서는 하나의 entity의 속성을 저장하기 위해 한 개 이상의 테이블을 사용한다. 그리고 그 테이블의 주제를 entity라고 표현한다.
예를 들어 회원관리 앱을 개발할 때는 users라는 entity(table)을 있을 수 있다. 또한 특정 user의 address를 저장하기 위해서 addresses라는 entity(table)가 필요하다.
entity개념이 잘 와닿지 않는데, 자꾸 나오는 단어라 헷갈린다면 이렇게 이해해보자.
entity = 테이블, entity의 객체 = 테이블의 row다.
사실 이거 말고도 entity의 특징, 속성, 종류 등과 같은 개념을 더 공부할 필요가 있지만, 본 글에서는 테이블의 관계에 대해 집중하기 때문에 간단하게만 소개해봤다.
관계(Relationship)
데이터베이스에서는 두 개의 엔티티간의 관계를 정의해야 한다. 이처럼 관계를 표기하기 위해서는 아래 3가지 개념이 필수적이다.
1) 관계명 : 관계의 이름
관계명이란 엔티티가 관계에 참여하는 형태를 지칭하는 이름으로, 두 개의 엔티티에 대한 것이기 때문에 하나의 관계는 2개의 현재형으로 표현한 관계명을 가진다.
2) 관계 차수
두 개의 엔티티 관계에서 참여자의 수를 표현하는 것을 관계차수라고 한다. 가장 일반적인 관계차수 표현방법은 1:1, 1:N, N:N이다. 관계 차수에서 가장 중요하게 고려해야 할 사항은, 한 쪽 엔티티와 관계를 맺은 엔티티 쪽이 하나의 객체만을 가지는지, 혹은 여러 개의 객체를 가질 수 있는지를 파악하는 것이 중요하다.
위 그림은 이와 같은 관계 차수와 관련하여 그린 ERD(Entity Relationship Diagram)으로, 엔티티간의 관계를 시각화한 다이어그램을 말한다. 데이터베이스를 다루다보면 한번쯤은 그려볼법한 다이어그램이다.
3) 관계 선택 사양(Optionality)
관계에서 항상 참여해야 하는지(필수관계인지) 아니면 참여할 수도 있는지(선택관계인지)에 대한 것을 관계 선택 사양이라고 한다. 아래처럼 엔티티가 항상 관계에 참여하는지 (필수 참여) 아니면 선택적으로 관계에 참여하는지(선택참여)를 구분할 수 있어야 한다.
위 그림을 예로 들어보면, 학생 엔티티는 수업 엔티티와 관계가 있을 수도 있고, 없을 수도 있다. 따라서 학생 엔티티를 기준으로 학생-수업 관계는 선택참여가 된다. 따라서 동그라미 표시를 했다. 반대로 학생이 듣지 않는 수업에 대해서는 고려하지 않는 경우에, 수업 엔티티는 학생 엔티티와의 관계가 필수적이기 때문에 수업 엔티티를 기준으로 수업-학생 관계는 필수 참여가 된다. 따라서 이때는 작대기를 하나 그어서 표시했다.
Keys
key는 테이블의 관계와 유일성(uniqueness)을 설정하는 constraint중 하나다.
1) 테이블의 특정 row(record, tuple)를 식별하거나
2) 다른 테이블의 특정 row(record, tuple)를 참조할 때 사용된다.
가장 대표적인 두 종류의 key는 Primary Key(PK)와 Foreign Key(FK)가 있다. 사실 이거 외에도 후보키, 대체키, 슈퍼키 다른 종류의 키가 있는데, 이건 나중에 다루도록 하겠다.
Primary Key (기본 키)
Primary Key는 이름에서 알 수 있듯이 테이블에서 가장 기본적인 속성을 의미한다. 테이블에서 특정 row(tuple/record)를 식별하기 위해 사용되는 값이다. 데이터베이스에서 테이블을 생성할 때 하나 또는 그 이상의 항목을 기본키로 설정할 수 있다.
식별자로 동작하기 위해서는 아래의 제약이 필수적이다
1) NOT NULL : 아무런 값이 없는 상태 값인 null 값을 가질 수 없다.
2) UNIQUE : 다른 row와 중복되어 나타낼 수 없는 단일 값을 가진다.
여기서 주의 할 것은, 테이블에서 하나만 가질 수 있기 때문에 똑같이 NOT NULL, UNIQUE한 constraint를 가지고 있다고 하더라도 모두가 기본키가 될 수 없다.
그리고 일반적으로는 id라는 필드를 primary key로 지정하는 경우가 많다.
primary key를 설정하는 방법은 아래 두가지 방법이 있다. (사실 데이터베이스마다 다르고, 더 있다)
방법 1)
CREATE TABLE {TABLE NAME}
(
{field name} {field type} PRIMARY KEY,
...
)
#예:
CREATE TABLE Persons
(
UserId INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Job VARCHAR(100),
);방법 2)
CREATE TABLE {TABLE NAME}
(
{field name} {field type},
...,
CONSTRAINT {constraint name} PRIMARY KEY ({field name}...)
)
# 예:
CREATE TABLE Persons (
UserId INT NOT NULL,
Name VARCHAR(100) NOT NULL,
Job VARCHAR(100),
CONSTRAINT PK_Id PRIMARY KEY (UserId)
);
#복합키를 PRIMARY KEY로 지정하는 예
CREATE TABLE users_books (
user_id int NOT NULL ,
book_id int NOT NULL,
checkout_date timestamp,
return_date timestamp,
PRIMARY KEY (user_id, book_id),
FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE,
FOREIGN KEY (book_id) REFERENCES books(id) ON UPDATE CASCADE
);Foreign Key (외래 키)
기본적으로 FOREIGN KEY 제약 조건을 설정한 필드를 외래키라고 부르며, 한 테이블을 다른 테이블과 연결해주는 역할을 한다. 관계를 맺고 있는 테이블 중 참조되는 릴레이션의 기본키 필드 혹은 UNIQUE 제약조건이 설정된 필드와 대응되어 릴레이션간에 참조 관계를 표현하는 속성을 의미한다.
Foreign Key를 설정하는 방법은 아래와 같다.
CREATE TABLE {table name}
(
{field name} {field type},
...,
FOREIGN KEY ({field name}) REFERENCES {another table name}({another table's field name})
)
# 예:
CREATE TABLE Persons (
UserId INT NOT NULL,
Name VARCHAR(100) NOT NULL,
ClassId INT NOT NULL,
PRIMARY KEY (UserId),
FOREIGN KEY (ClassId) REFERENCES classes(ClassID)
);
#복합 키를 외래키로 지정하는 예
CREATE TABLE sub_accounts (
sub_acc INTEGER PRIMARY KEY,
ref_num INTEGER NOT NULL,
ref_type INTEGER NOT NULL,
sub_descr CHAR(20),
FOREIGN KEY (ref_num, ref_type) REFERENCES accounts(acc_num, acc_type));
Foreign key를 사용하는 경우의 예를 들어보겠다.
colors라는 테이블과 shapes라는 테이블이 있다고 해보자. 이 때 shape가 여러개의 color를 가질 수 있다고 했을 때 아래와 같이 shapes에 color_id라는 column을 colors테이블의 foreign key로 설정하여 아래 그림과 같은 관계를 만들 수 있다.
Referential Integrity 참조무결성
referential integrity란 참조관계에 있는 2개의 테이블간의 데이터 무결성, 즉 데이터가 항상 일간된 값을 유지되는 것을 의미한다. 쉽게 말해 참조되는 테이블의 기본키에 대한 외래키가 존재하는 한 해당 row는 수정되거나 삭제될 수 없다. 만약 해당 레코드를 수정하거나 삭제하면, 다른 테이블의 외래키가 삭제되거나 수정된 레코드를 가리켜 참조 무결성 제약에 위배가 되기 때문이다.
예를 들어 shapes테이블의 color_id가 Foreign key이고 그 값이 '1'이면 실제로 colors 테이블에 '1'이 있어야 하고 두 값은 항상 같아야 한다. 만약 변경해야 하거나 삭제해야 하는 상황에는 Cascade Action을 사용해야 한다.
이렇게 참조 무결성 개념이 있는 이유는 다른 테이블에 존재하지 않는 데이터를 참조하지 않도록 보장하고, 사용자의 실수로 인해 관련 데이터가 삭제되거나 수정되는 것을 막기 위해서다.
이처럼 Foreign key를 통해 두 개의 테이블간의 관계를 설정할 수 있는데, 이제부터는 3개의 기본적인 테이블간의 관계에 대해 알아보자.
1) 1 : 1 : 한 유저는 하나의 주소를 가진다
2) 1 : N : 한 권의 책은 여러 개의 리뷰를 가질 수 있다.
3) N : N : 한 유저는 여러 권의 책을 예약할 수 있고, 한 권의 책은 여러 명의 유저에게 대여될 수 있다.
1 : 1 (일대일 관계)
1 : 1 관계란 참조하는 테이블의 row(entity 객체)와 참조되는 table의 row(entity 객체)가 단 하나의 관계를 가지는 것을 의미한다.
현실적인 예는 아니지만 한 유저는 하나의 address만 가질 수 있고, 하나의 address는 하나의 user만 가질 수 있는 경우가 그 예가 될 수 있다.
테이블 생성 statement 예시는 아래와 같다
CREATE TABLE users (
id serial,
username VARCHAR(25) NOT NULL,
enabled boolean DEFAULT TRUE,
last_login timestamp NOT NULL DEFAULT NOW(),
PRIMARY KEY (id)
);
/*
one to one: User has one address
*/
CREATE TABLE addresses (
user_id int NOT NULL,
street VARCHAR(30) NOT NULL,
city VARCHAR(30) NOT NULL,
state VARCHAR(30) NOT NULL,
PRIMARY KEY (user_id),
CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users (id)
);1 : N (일대다 관계)
1:N 관계는 참조되는 테이블의 한 row(entity 객체)가 참조하는 테이블의 여러 row(entity 객체)를 가질 수 있는 관계를 의미한다. 여기서 주의 할 점은 참조되는 테이블의 row는 한개라는 점이다. 1:N관계는 실제 DB를 설계할 때 자주 사용되는 관계다.
예를 들어
1) users = 참조되는 테이블, reviews = 참조하는 테이블 : 한 유저는 여러개의 리뷰를 쓸 수 있고, 리뷰는 여러 명의 유저를 가질 수 있다. (
2) books = 참조되는 테이블, reviews = 참조하는 테이블 : 한 권의 책은 여러 개의 리뷰를 가질 수 있고, 리뷰는 여러 권의 책을 가질 수 있다.
테이블 생성 statement 예시는 아래와 같다.
CREATE TABLE books (
id serial,
title VARCHAR(100) NOT NULL,
author VARCHAR(100) NOT NULL,
published_date timestamp NOT NULL,
isbn int,
PRIMARY KEY (id),
UNIQUE (isbn)
);
/*
one to many: Book has many reviews
*/
DROP TABLE IF EXISTS reviews;
CREATE TABLE reviews (
id serial,
book_id int NOT NULL,
user_id int NOT NULL,
review_content VARCHAR(255),
rating int,
published_date timestamp DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);N : N (다대다 관계)
N : N 관계는 관계를 가진 양쪽 테이블 모두에서 1 : N 관계를 가지는 것을 의미한다. 즉, 서로가 서로를 1 : N 관계로 보는 것이다.
예를 들어보면 한 유저는 여러 개의 책을 예약할 수 있고, 한 권의 책은 여러 유저에 의해 대여돼왔을 것이다. 이런 경우가 바로 N : N 관계가 될 수 있다.
N : N 관계의 경우에는 두 개의 테이블의 관계를 이어주는 제 3의 cross reference table이 별도로 필요하다. 이 테이블은 두 테이블의 대표키를 컬럼으로 갖는다.
테이블 생성 예시
CREATE TABLE users_books (
user_id int NOT NULL,
book_id int NOT NULL,
checkout_date timestamp,
return_date timestamp,
PRIMARY KEY (user_id, book_id),
FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE,
FOREIGN KEY (book_id) REFERENCES books(id) ON UPDATE CASCADE
);위 테이블에서 주의해야할점은 primary key가 user_id, book_id 두 개의 컬럼으로 이루어진 복합키라는 점이다. 따라서 두 개의 조합은 uniue해야 한다.
FAQ?
Q : foreign key로 primary key는 아니지만 unique constraint을 가진 column을 설정할 수 있나?
A : 가능하다. foreign key constraint는 꼭 다른 테이블에서 primary key constraint를 가진 column일 필요는 없다. 단, unique constraint는 가지고 있어야 한다.
참고자료
https://dataonair.or.kr/db-tech-reference/d-guide/sql/?mod=document&uid=328
https://launchschool.com/books/sql_first_edition/read/multi_tables#tablerelationships