SQL tutorial with MySQL

Recommanded Free YOUTUBE Lecture: <% selectedImage[1] %>

yundream
2023-04-07
2023-04-07
2737

## MySQL Foreign key
Foreign key(외래)는 두 테이블간의 관계를 설정하는데 사용되는 데이터베이스 필드로 다른 테이블의 **Primary Key를 참조**한다. Foreign Key가 테이블에 정의되면 다른 테이블의 Primary Key 데이터를 참조해서 쿼리가 수행된다. Primary Key를 가지고 있는 테이블은 **parent 테이블**, 해당 primary key를 참조하는 foregin key를 가지고 있는 테이블을 **child 테이블 ** 이라고 한다.

아래 그림은 Primary Key와 Foreign Key 그리고 테이블의 관계를 묘사하고 있다. 
![Foreign Key](https://docs.google.com/drawings/d/e/2PACX-1vQg6Sc0qTYA1tV-yPpwmVVn60orX5EUvOe_NtDtGv3C2EvUtG8eWCtXLwyg5zhXqOagItEJY_YyUKEQ/pub?w=934&h=339)

User 테이블의 Primary key는 user_id 다. Order 테이블은 주문 정보를 담고 있는데, 주문한 유저를 식별해야 하기 때문에 user_id Foreign key로 설정했다. 즉 user_id 속성으로 User 테이블과 Order 테이블이 서로 연결된다.

User 테이블이 Primary Key를 가지고 있으므로 parent 테이블이 되며, Order 테이블이 foregin key로 참조하고 있으므로 child 테이블이 된다.

## 예제 데이터베이스 & 테이블 만들기
테스트를 위해서 데이터베이스와 테이블을 만들었다. Parent 테이블인 User 테이블을 만들었다. 
```sql
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE User (
    user_id int NOT NULL PRIMARY KEY,
    name varchar(32),
    email varchar(64)
);
```
User 테이블에 테스트용 데이터를 입력한다.
```sql
INSERT INTO User SET user_id = 201, name="john", email="j@mail.com";
INSERT INTO User SET user_id = 202, name="shawn", email="s@mail.com";
INSERT INTO User SET user_id = 203, name="smith", email="m@mail.com";
```

## Foreign Key 만들기
이제 User의 user_id를 참조하는 Foreign Key를 가지는 Orders 테이블을 만들어보자.
```sql
CREATE TABLE Orders (
    order_id int NOT NULL PRIMARY KEY,
    product_name varchar(128),
    user_id int NOT NULL,
        CONSTRAINT FK_UserID FOREIGN KEY (user_id) REFERENCES User(user_id)
);
```

## Foreign Key 삭제
아래와 같이 Foreign Key를 삭제할 수 있다.
```sql
ALTER TABLE table_name
DROP FOREIGN KEY 
constraint_name;
```
* table_name: Foreign Key를 가진 테이블 이름 
* constraint_name: Foreign Key 제약조건 이름
앞서 만든 Foreign Key를 삭제해보자.

```sql
ALTER TABLE Orders
DROP FOREIGN KEY
FK_UserID;
```

### Foreign Key 정보 확인
아래 쿼리를 이용해서 특정 테이블(parent 테이블)을 참조하고 있는 모든 외래키 목록을 조회할 수 있다.
```sql
SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = 'testdb' AND
  REFERENCED_TABLE_NAME = 'User';
```
아래와 같은 결과를 확인 할 수 있을 것이다.
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
|------------|-------------|-----------------|-----------------------|------------------------|
| Orders     | user_id     | Orders_ibfk_1   | User                  | user_id                |

## Foreign Key Constraint
외래키는 테이블간의 관계를 맺음으로써 JOIN 성능을 높이고 **무결성을 높이기 위해서** 사용한다.

![Foreign 제약조건](https://docs.google.com/drawings/d/e/2PACX-1vQ6YeNCbesrDZNsRePqvqc9MHLzjaraXUXzvKIemuQX_rcJWfc2sxC_5mMNOkvN1O2GRuxF4pjn8GS1/pub?w=923&h=242)

위 테이블에서 "john(201)" 유저는 두 개의 상품을 주문한 상태다. 이때 유저 탈퇴등의 이유로 join을 삭제하면 "Order 테이블에는 존재하지 않는 유저의 주문"이 남게 되면서 데이터 무결성이 깨져버린다. 이 경우에 john 유저를 삭제하려고 하면 "order 테이블에 john 유저의 주문이 없있으므로 실패"해야 한다.

Foreign Key **제약조건을 설정하여** 데이터의 변경, 즉 업데이트와 삭제가 발생했을 때 무결성을 보장하기 위한 제약 조건을 설정할 수 있다.

```sql
ON UPDATE {RESTRICT | CASCADE | NO ACTION | SET NULL}
ON DELETE {RESTRICT | CASCADE | NO ACTION | SET NULL}
```

* CASCADE: Parent 테이블의 PK를 변경하면, 해당 PK를 참조하는 child table의 모든 레코드가 변경된다.
* RESTRICT: Parent 테이블의 PK를 변경하려고 할 때, 해당 PK를 참조하는 레코드가 있다면 명령이 취소된다.
* NO ACTION: 기본 제약조건이 설정된다. MySQL의 기본 제약조건은 RESTRICT 이다.
* SET NULL: Parnet 테이블의 PK를 변경하면, 참조하는 모든 레코드의 값은 NULL이 된다.

#### 제약조건 테스트
아래의 쿼리를 실행해보자.
```sql
INSERT INTO Orders SET order_id = 100, product_name='mac-m2', user_id=303;
```
참조하는 User 테이블에 303 user_id가 없기 때문에 에러가 발생한다. 
```
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testdb`.`Orders`, CONSTRAINT `Orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `User` (`user_id`))
```
이제 올바른 데이터를 입력해보자.
```sql
INSERT INTO Orders SET order_id = 100, product_name='mac-m2', user_id=201;
INSERT INTO Orders SET order_id = 101, product_name='lenovo-think', user_id=201;
INSERT INTO Orders SET order_id = 102, product_name='hp-i31', user_id=203;
```

**Restrict 제약조건**
User 테이블에서 user_id가 201인 레코드를 삭제해보자.
```sql
DELETE FROM User WHERE user_id=201;
```
Orders 테이블에 201을 참조하고 있는 레코드가 있으므로 실패한다.
```
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`Orders`, CONSTRAINT `Orders_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `User` (`user_id`))
```

**CASCADE 제약조건**
CASCADE는 parent 테이블의 컬럼이 변경되면 child 테이블에도 적용된다. user_id가 삭제되면, Order의 레코드도 삭제되도록 외래키 제약조건을 걸어보자.
```sql
ALTER TABLE Orders ADD FOREIGN KEY(user_id) references User(user_id) ON DELETE CASCADE;
```
이제 "john" 유저를 삭제하면, john의 주문도 삭제된다.
```sql
 DELETE from User 
 WHERE user_id=201;
```
```
> select * from Orders;
+----------+--------------+---------+
| order_id | product_name | user_id |
+----------+--------------+---------+
|      102 | hp-i31       |     203 |
+----------+--------------+---------+
```

**SET NULL 제약조건**
SET NULL은 parent 테이블의 컬럼이 변경되면 child 테이블의 레코드 값이 NULL이 된다.  
```sql
ALTER TABLE Orders 
ADD FOREIGN KEY(user_id) references User(user_id) 
ON DELETE SET NULL;
```

## 정리
[SQL Study With MySQL](https://www.joinc.co.kr/w/sql_study_with_mysql_index) 에서 MySQL과 관련된 다른 문서들을 읽을 수 있습니다.

Search For:

BY TAGS

Recent Posts

Archive Posts

Tags

About

Get in Touch

Categories