SQL - MySQL, 데이터베이스 정규화, Foreign Key, JOIN

MySQL

 

 

✅ 데이터베이스 정규화란? (Normalization)

---

데이터 중복을 줄이고, 구조를 깔끔하게 만들어 무결성을 유지하는 설계 방법

 

① 제1정규형 (1NF)

컬럼은 원자값(Atomic)만 가져야 한다

 

 

 

② 제2정규형 (2NF)

부분 함수 종속 제거

 

 

③ 제3정규형 (3NF)

이행적 종속 제거

 

 

✅ Foreign Key

---

다른 테이블의 Primary Key를 참조하는 Column

 

CREATE TABLE dogs (
    dog_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL, weight DECIMAL(5, 2),
    date_of_birth DATE,
    owner_id BIGINT UNSIGNED,
    breed_id BIGINT UNSIGNED
);

owner_id와 breed_id는 Foreign Key

 

Foreign Key Constraint

CREATE TABLE dogs (
	dog_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(50) NOT NULL,
	weight DECIMAL(5, 2),
	date_of_birth DATE,
	owner_id BIGINT UNSIGNED,
	breed_id BIGINT UNSIGNED,
	FOREIGN KEY (owner_id) REFERENCES owners (owner_id),
	CONSTRAINT breed_fk FOREIGN KEY (breed_id) REFERENCES breeds (breed_id)
);

Foreign Key Constraint도 이름을 지정해줄 수 있다.(추후에 제약을 삭제하고 싶을 때 이름으로 삭제할 수 있다.)

 

 

ON DELETE

FOREIGN KEY (owner_id) REFERENCES owners (owner_id) ON DELETE CASCADE,

owner가 삭제 됐을 때 해당 dog도 삭제.

(owner_id가 update 되었을 때 설정이 가능한 ON UPDATE도 있지만 id가 업데이트 되는 경우는 드물다.)

 

FOREIGN KEY (owner_id) REFERENCES owners (owner_id) ON DELETE SET NULL,

owner가 삭제 됐을 때 해당 dog에서 owner_id 값을 null로 설정

(NOT NULL 제약이 없어야 한다.)

 

owner_id BIGINT UNSIGNED,
breed_id BIGINT UNSIGNED DEFAULT 2,
FOREIGN KEY (owner_id) REFERENCES owners (owner_id) ON DELETE SET NULL,
CONSTRAINT breed_fk FOREIGN KEY (breed_id) REFERENCES breeds (breed_id) ON DELETE SET DEFAULT

breed가 삭제 됐을 때 해당 dog의 breed_id 값을 기본값(2)으로 설정

(MySQL에서는 SET DEFAULT를 지원하지 않는다.)

 

CREATE TABLE dogs (
	dog_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(50) NOT NULL,
	weight DECIMAL(5, 2),
	date_of_birth DATE,
	owner_id BIGINT UNSIGNED,
	breed_id BIGINT UNSIGNED DEFAULT 2,
	-- FOREIGN KEY (owner_id) REFERENCES owners (owner_id) ON DELETE SET NULL,
	CONSTRAINT breed_fk FOREIGN KEY (breed_id) REFERENCES breeds (breed_id) ON DELETE SET DEFAULT
);

ALTER TABLE dogs CONSTRAINT owner_fkFOREIGN KEY (owner_id) REFERENCES owners (owner_id) ON DELETE SET NULL;

제약을 새로 추가해야 할 때 dogs table을 굳이 삭제하고 다시 추가할 필요없이 다음과 같이 추가가 가능하다.

 

ALTER TABLE dogs
DROP FOREIGN KEY owner_fk,
CONSTRAINT owner_fk FOREIGN KEY (owner_id) REFERENCES owners (owner_id) ON DELETE SET NULL;

Foreign Key 제약 조건을 수정할 때는 먼저 삭제했다가 다시 설정을 한다.

(PostgreSQL에서는 DROP CONSTRAINT를 사용하지만, MySQL에서는 DROP FOREIGN KEY로 Foreign Key를 삭제해야 한다.)

 

 

✅ One to Many, Many to One

---

1. One-To-Many (1:N) 관계
한 테이블의 하나의 레코드가, 다른 테이블의 여러 레코드와 연결되는 관계
(1:N은 외래 키만 있으면 표현 가능)
ex) 한 명의 주인은 여러 마리 강아지를 가질 수 있다.

2. One-To-One (1:1) 관계
두 테이블 사이에서, 하나의 레코드가 다른 테이블의 오직 하나의 레코드와만 연결되는 관계
(1:1은 외래 키 + UNIQUE 조건이 있어야 강제 가능)
ex) 한 마리의 강아지는 하나의 여권만 가질 수 있다.

 

 

✅ Many to Many

---

SQL은 다 대 다 관계(N:N relationship)를 정의할 수 없어서 중간 테이블(bridge table, link table)을 만들어야 한다.

 

CREATE TABLE tricks (trick_id BIGINT UNSIGNED PRIMARY KEY auto_increment, name VARCHAR(50) UNIQUE NOT NULL, difficulty ENUM('easy', 'medium', 'hard') NOT NULL DEFAULT 'easy');

CREATE TABLE dog_tricks (
	dog_id BIGINT UNSIGNED,
	trick_id BIGINT UNSIGNED,
	proficiency ENUM('begginer', 'intermediate', 'expert') NOT NULL DEFAULT 'begginer',
	date_learned TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (dog_id, trick_id),
	FOREIGN KEY (dog_id) REFERENCES dogs (dog_id) ON DELETE CASCADE,
	FOREIGN KEY (trick_id) REFERENCES tricks (trick_id) ON DELETE CASCADE
);

dog_id와 trick_id의 조합을 Primary Key로 사용하여 둘의 조합이 unique해야함을 정의한다.

(동일한 dog이 동일한 trick을 중복으로 배울 수 없다.)

 

 

✅ JOIN

---

여러 테이블을 연결해 하나의 결과로 만들 수 있다.

CROSS JOIN

별로 유용하지 않다.

첫 번째 테이블의 모든 row와 두 번째 테이블의 모든 row를 각각 연결시킨다.

SELECT
	*
FROM
	dogs
	CROSS JOIN owners;

use case: 개와 주인 사이의 모든 조합을 알아보고 싶을 때

 

INNER JOIN

가장 많이 사용하게 될 JOIN

SELECT
	*
FROM
	dogs
	INNER JOIN owners;

INNER 없이 JOIN만 사용해도 INNER를 적은 것과 동일하게 작동한다.

 

SELECT
	*
FROM
	dogs
	JOIN owners;

 

조건을 적어서 row를 매칭시킬 수 있다.

SELECT
	*
FROM
	dogs
	JOIN owners ON dogs.owner_id = owners.owner_id;

dogs 테이블의 owner_id와 owners 테이블의 owner_id가 같은 row들만 매칭

조건을 적지 않으면 JOIN은 CROSS JOIN이 된다.

 

USING

SELECT
	dogs.name AS dog_name,
	owners.name AS owner_name,
	breeds.name AS breed_name
FROM
	dogs
	JOIN owners USING (owner_id)
	JOIN breeds ON dogs.breed_id = breeds.breed_id;

ON으로 조건을 작성할 때 같은 column 이름을 사용하면 USING으로 짧게 작성할 수 있다.

 

 

OUTER JOIN

2가지 종류가 있는데 유용하다.

1. LEFT OUTER JOIN (LEFT JOIN)

2. RIGHT OUTER JOIN (RIGHT JOIN)

(LEFT 혹은 RIGHT을 적으면 OUTER는 필수 키워드가 아니다.)

 

애매하거나 의미가 불분명한 row를 확인해야 할 때 사용한다.

SELECT
	dogs.name AS dog_name,
	owners.name AS owner_name
FROM
	dogs
	LEFT JOIN owners USING (owner_id);

LEFT에 해당하는 dogs의 모든 row를 가져오고 매칭 되는 것은 매칭 시키고 owner_id 값이 null인 dog도 표시해준다.

SQLite에서는 CROSS JOIN, INNER JOIN, LEFT OUTER JOIN만 지원한다.

 

 

✅ SELECT문의 값으로 column 채우기

---

INSERT INTO
	statuses (status_name)
SELECT
	status
FROM
	movies
GROUP BY
	status;

 

 

✅ 기존 Table에 Foreign key를 가지는 새 column 생성하기

---

ALTER TABLE movies
ADD COLUMN status_id BIGINT UNSIGNED;

ALTER TABLE movies
ADD CONSTRAINT fk_status FOREIGN KEY (status_id) REFERENCES statuses (status_id) ON DELETE SET NULL;

 

그리고 그 column에 movies table에서 status column과 같은 이름을 가지는 status를 statuses table의 status_id를 가져와 방금 만든 새 column에 넣어주기

UPDATE movies
SET
	status_id = (
		SELECT
			status_id
		FROM
			statuses
		WHERE
			status_name = movies.status
	);

 

마지막으로 기존 movies의 status column을 삭제해주면 된다.

ALTER TABLE movies
DROP COLUMN status;