SQL 데이터베이스 입문_ 6. 관계 만들기

olongun·2025년 4월 22일

db sql

SQL 데이터베이스 입문

목록 보기

6/10

6.1 관계란

6.1.1 관계의 개념

관계란 여러 테이블에 분산 저장된 데이터가 서로 어떻게 연결돼 있는지를 정의하는 개념

일대일 관계는 국가-수도 관계처럼 하나의 데이터가 하나의 데이터와만 연결된 관계
일대다 관계는 부모-자식 관계처럼 하나의 데이터가 여러 데이터와 연결된 관계
다대다 관계는 여러 데이터가 여러 데이터와 연결된 관계

6.1.2 관계를 만드는 키

기본키(Primary Key, PK)란 테이블 내에서 모든 튜플을 구분할 수 있는 칼럼 또는 칼럼의 조합으로 다음 4가지 특징을 만족해야 한다

유일성: 기본키 값은 테이블 내에서 유일한 값을 가져야 한다.

비 널: 기본키 값으로 NULL이 올 수 없고, 반드시 값이 저장돼야 한다.

불변성: 기본키 값은 한 번 설정되면 변경할 수 없다.

식별성: 기본키 값은 각 튜플을 구분하는 명확한 기준 역할을 해야 한다.

외래키(Foreign Key, FK)란 다른 테이블의 기본키를 가리키는 칼럼으로 기본키와 달리 NULL을 허용

6.1.3 제약 조건

AUTO_INCREMENT: 칼럼의 값을 자동으로 1씩 증가시킨다. 정수형 칼럼에 붙일 수 있으며 주로 기본키와 함께 사용된다.
UNIQUE: 칼럼의 값으로 고유한 값만 허용한다. 중복 값을 입력하면 에러가 난다.
NOT NULL: 칼럼에 NULL을 허용하지 않는다.
DEFAULT: 칼럼에 기본값을 지정한다. 해당 칼럼에 입력값이 주어지지 않으면 기본으로 지정된 값이 삽입된다.
CHECK: 칼럼의 값이 특정 조건을 만족하도록 조건을 지정한다.
UNSIGNED: 숫자형 칼럼에서 음수를 허용하지 않고 양수 값만 저장하도록 제한한다.

-- 제약 조건의 사용 예
CREATE TABLE users
	id INTEGER AUTO_INCREMENT,			  -- 아이디(자동으로 1씩 증가)
    email VARCHAR(100) UNIQUE,			  -- 이메일(고유한 값만 허용)
    name VARCHAR(50) NOT NULL,			  -- 사용자명(NULL을 허용하지 않음)
    status VARCHAR(10) DEFAULT 'active',  -- 계좌 상태(기본값은 'active')
    balance INTEGER UNSIGNED,			  -- 계좌 잔액(음수를 허용하지 않음)
    age INTEGER CHECK (age >= 18),		  -- 나이(18세 이상만 허용)
    PRIMARY KEY (id)					  -- 기본키 지정: id

6.2 다양한 관계 만들기

외래키를 이용해 일대일 관계, 일대다 관계, 다대다 관계 만들어보겠다.
새 DB인 relation DB를 만들고 진입

-- relation DB 생성 및 진입
CREATE DATABASE relation;
USE relation;

6.2.1 일대일 관계 만들기

테이블을 만들 때 외래키를 지정하는 방법

CREATE TABLE 테이블명 (
	칼럼명1 자료형1,
    ...
    PRIMARY KEY (칼럼명),
    FOREIGN KEY (칼럼명) REFERENCES 연결대상_테이블(연결대상_테이블의_기본키)
);

일대일 관계의 두 테이블을 만들어 보자

-- countries 테이블 생성
CREATE TABLE countries (
	id INTEGER,			-- 아이디
    name VARCHAR(255),	-- 국가명
    PRIMARY KEY (id)	-- 기본키 지정: id
);

-- capitals 테이블 생성
CREATE TABLE capitals (
	id INTEGER,					-- 아이디
    name VARCHAR(255),			-- 수도명
    country_id INTEGER UNIQUE,	-- 국가 아이디(고유한 값만 허용)
    PRIMARY KEY (id),			-- 기본키 지정: id
    FOREIGN KEY (country_id) REFERENCES countries(id) -- 외래키 지정: country_id
);

-- countries 데이터 삽입
INSERT INTO countries (id, name)
VALUES
	(1, 'South Korea'),
    (2, 'United States'),
    (3, 'Japan');

-- capitals 데이터 삽입
INSERT INTO capitals (id, name, country_id)
VALUES
	(101, 'Seoul', 1),
    (102, 'Washington D.C.', 2),
    (103, 'Tokyo', 3);

6.2.2 일대다 관계 만들기

-- teams 테이블 생성
CREATE TABLE teams (
	id INTEGER,			-- 아이디
    name VARCHAR(255),	-- 팀명
    PRIMARY KEY (id)	-- 기본키 지정: id
);

-- players 테이블 생성
CREATE TABLE players (
	id INTEGER,				-- 아이디
    name VARCHAR(255),		-- 선수명
    team_id INTEGER,		-- 소속팀 아이디
    PRIMARY KEY (id),		-- 기본키 지정: id
    FOREIGN KEY (team_id) REFERENCES teams(id)	-- 외래키 지정: team_id
);

-- teams 데이터 등록
INSERT INTO teams (id, name)
VALUES
	(1, 'FC Awesome'),
    (2, 'Winners United');

-- players 데이터 등록
INSERT INTO players (id, name, team_id)
VALUES
	(1, 'John Doe', 1),
    (2, 'Jane Smith', 1),
    (3, 'Max Payne', 2),
    (4, 'Alex Johnson', 2),
    (5, 'Sara Connor', 2);

6.2.3 다대다 관계 만들기

이 관계는 A, B 두 테이블을 직접 연결하는 것이 아니라 중간 테이블인 C 테이블을 이용해 만든다.

-- doctors 테이블 생성
CREATE TABLE doctors (
	id INTEGER,			-- 아이디
    name VARCHAR(255),	-- 의사명
    PRIMARY KEY (id)	-- 기본키 지정: id
);

-- patients 테이블 생성
CREATE TABLE patients (
	id INTEGER,			-- 아이디	
    name VARCHAR(255),	-- 환자명
    PRIMARY KEY (id)	-- 기본키 지정: id
);

-- 중간 테이블인 appointments 테이블 생성
CREATE TABLE appointments (
	id INTEGER,			-- 아이디
    doctor_id INTEGER,	-- 의사 아이디
    patient_id INTEGER,	-- 환자 아이디
    date DATE,			-- 진료 일자
    PRIMARY KEY (id),	-- 기본키 지정: id
    FOREIGN KEY (doctor_id) REFERENCES doctors(id),	 -- 외래키 지정: doctor_id
    FOREIGN KEY (patient_id) REFERENCES patients(id) -- 외래키 지정: patient_id
);

-- doctors 데이터 삽입
INSERT INTO doctors (id, name)
VALUES
    (1, '김 닥터'),
    (2, '이 닥터'),
    (3, '최 닥터');

-- patients 데이터 삽입
INSERT INTO patients (id, name)
VALUES
    (1, '환자 A'),
    (2, '환자 B'),
    (3, '환자 C');

-- appointments 데이터 삽입
INSERT INTO appointments (id, doctor_id, patient_id, date)
VALUES
    (1, 1, 1, '2025-01-01'),
    (2, 1, 2, '2025-01-02'),
    (3, 2, 2, '2025-01-03'),
    (4, 2, 3, '2025-01-04'),
    (5, 3, 3, '2025-01-05'),
    (6, 3, 1, '2025-01-06');

6.3 관계 만들기 실습: 별그램 DB

6.3.1 별그램 DB의 개요

별그램 DB에 생성할 테이블은 총 5개

users(사용자): 사용자의 '아이디', '닉네임', '이메일'을 저장
photos(사진): 사진의 '아이디', '파일명', 게시자 아이디'를 저장
comments(댓글): 댓글의 '아이디', '본문', '작성자 아이디', '댓글이 달린 사진 아이디'를 저장
settings(개인 설정): 사용자 개인 설정의 '아이디', '계정 공개 여부', '계정 추천 여부', '사용자 아이디'를 저장
likes(좋아요): 좋아요의 '아이디', '좋아요를 누른 사용자 아이디', '좋아요를 받은 사진 아이디'를 저장

별그램 DB의 관계도

일대일 관계: '사용자'는 개인별로 하나의 '개인 설정' 값만 가질 수 있습니다.
일대다 관계: '사용자'는 여러 장의 '사진'을 게시할 수 있습니다.
다대다 관계:
- '사용자'는 여러 '사진'에 댓글을 작성할 수 있고, '사진' 또한 여러 '사용자'로부터 댓글을 받을 수 있습니다.
- '사용자'는 여러 '사진'에 좋아요를 누를 수 있고, '사진' 또한 여러 '사용자'로부터 좋아요를 받을 수 있습니다.

6.3.2 ~ 6.3.6 테이블 만들기

-- stargram DB 생성 및 진입
CREATE DATABASE stargram;
USE stargram;

-- 6.3.2 users 테이블 생성
CREATE TABLE users (
	id INTEGER AUTO_INCREMENT,
    nickname VARCHAR(30),
    email VARCHAR(255),
    PRIMARY KEY (id)
);

-- users 데이터 삽입(id는 자동 증갓값으로 입력되므로 생략)
INSERT INTO users (nickname, email)
VALUES
	('홍팍', 'sehongpark@cloudstudying.kr'),
    ('길벗', 'gilbut@cloudstudying.kr'),
    ('해삼', 'haesamq@cloudstudying.kr');
    
-- 데이터 조회
SELECT *
FROM users;

-- 6.3.3 photos 테이블 생성
CREATE TABLE photos (
	id INTEGER AUTO_INCREMENT,
    filename VARCHAR(255) NOT NULL,
    user_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- photos 데이터 삽입
INSERT INTO photos (filename, user_id)
VALUES
	-- 1번 사용자가 게시한 사진
    ('길고양이.jpg', 1),
    ('일몰.jpg', 1),
    ('은하계.jpg', 1),
    -- 2번 사용자가 게시한 사진
    ('백호.jpg', 2),
    ('검은 고양이 네로.jpg', 2),
    -- 사용자가 등록되지 않은 사진
    ('삭제된 이미지.jpg', NULL),
    ('제한된 이미지.jpg', NULL);
    
-- 데이터 조회
SELECT *
FROM photos;

-- 6.3.4 comments 테이블 생성
CREATE TABLE comments (
	id INTEGER AUTO_INCREMENT,
    body VARCHAR(1000),
    user_id INTEGER,
    photo_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (photo_id) REFERENCES photos(id)
);

-- comments 데이터 삽입
INSERT INTO comments (body, user_id, photo_id)
VALUES
	-- 1번 사진에 달린 댓글
    ('미야옹~', 1, 1),
    ('냐옹!', 2, 1),
    ('냥냥~', 3, 1),
    -- 2번 사진에 달린 댓글
    ('일몰이 멋지네요', 1, 2),
    ('해가 바다로 스윽~', 2, 2),
    -- 3번 사진에 달린 댓글
    ('안드로메다 성운인가?', 1, 3),
    ('성운이 아니라 은하임', 3, 3),
    -- 대상이 없는 댓글
    ('와우~', 3, NULL),
    ('오우야~', 3, NULL);
    
-- 데이터 조회
SELECT *
FROM comments;

-- 6.3.5 settings 테이블 생성
CREATE TABLE settings (
	id INTEGER AUTO_INCREMENT,
    private BOOLEAN NOT NULL,			   -- 계정 공개 여부
    account_suggestions BOOLEAN NOT NULL,  -- 계정 추천 여부
    user_id INTEGER UNIQUE,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- settings 데이터 삽입
INSERT INTO settings (private, account_suggestions, user_id)
VALUES
	(FALSE, FALSE, 1),
    (FALSE, TRUE, 2),
    (TRUE, TRUE, 3);
    
-- 데이터 조회
SELECT *
FROM settings;

-- 6.3.6 likes 테이블 생성
CREATE TABLE likes (
	id INTEGER AUTO_INCREMENT,
    user_id INTEGER,
    photo_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (photo_id) REFERENCES photos(id)
);

-- likes 데이터 삽입
INSERT INTO likes (user_id, photo_id)
VALUES
	(1, 1),
    (2, 1),
    (1 ,2),
    (2 ,2),
    (3 ,2),
    (1, 3),
    (3, 3),
    (NULL, 6),
    (NULL, 7);
    
-- 데이터 조회
SELECT *
FROM likes;