데이터베이스 (MySQL+AWS) - 외래키 (FOREIGN KEY)

4. 외래키 (FOREIGN KEY)

4.1. 데이터베이스 준비 (한글 설정도 다시 한번 코드로 상기하면서 데이터 넣기)

sqlDB 를 만들고, userTbl, buyTbl 두 테이블을 만듬

  • buyTbl 테이블의 다음 SQL 구문이 핵심
    • FOREIGN KEY (userID) REFERENCES userTbl(userID)
    • buyTbl 테이블의 userID 커럼은 userTbl 테이블의 userID를 참조함!

외래키(FOREIGN KEY)를 만드는 이유

  • 두 테이블 사이에 관계를 선언해서, 데이터의 무결성을 보장 (무결성? 다음 실습을 통해 이해해보자!)

실습 환경 구축

  • 다음 코드로 진행해보거나, 03_data/sqlDB.sql 을 Mysql Workbench 에서 실행하면 됨 (둘다 익숙해진다는 측면으로 해보세요)
db.close()
import requests
from bs4 import BeautifulSoup
import pandas as pd
host_name = "funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com"
username = "davelee"
password = "korea234"
database_name = "sakila"    # 데이터베이스 이름을 sakila 로 바꿔줘야 합니다.
db = pymysql.connect(
    host=host_name,  # DATABASE_HOST
    port=3306,
    user=username,  # DATABASE_USERNAME
    passwd=password,  # DATABASE_PASSWORD
    db=database_name,  # DATABASE_NAME
    charset='utf8'
)
cursor = db.cursor()
cursor.execute("set names utf8")
db.commit()
SQL_QUERY = """
DROP DATABASE IF EXISTS sqlDB;
CREATE DATABASE sqlDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

USE sqlDB;
DROP TABLE IF EXISTS userTbl;
CREATE TABLE userTbl (
    userID CHAR(8) NOT NULL PRIMARY KEY,
    name  VARCHAR(10) NOT NULL,
    birthYear INT NOT NULL,
    addr  CHAR(2) NOT NULL,
    mobile1 CHAR(3),
    mobile2 CHAR(8),
    height SMALLINT,
    mDate  DATE
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
DROP TABLE IF EXISTS buyTbl;
CREATE TABLE buyTbl (
    num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    userID CHAR(8) NOT NULL,
    prodName CHAR(4),
    groupName CHAR(4),
    price  INT NOT NULL,
    amount  SMALLINT NOT NULL,
    FOREIGN KEY (userID) REFERENCES userTbl(userID)
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO userTbl VALUES('LSG', '이승기', 1987, '서울', '011', '11111111', 182, '2008-8-8');
INSERT INTO userTbl VALUES('KBS', '김범수', 1979, '경남', '011', '22222222', 173, '2012-4-4');
INSERT INTO userTbl VALUES('KKH', '김경호', 1971, '전남', '019', '33333333', 177, '2007-7-7');
INSERT INTO userTbl VALUES('JYP', '조용필', 1950, '경기', '011', '44444444', 166, '2009-4-4');
INSERT INTO userTbl VALUES('SSK', '성시경', 1979, '서울', NULL, NULL, 186, '2013-12-12');
INSERT INTO userTbl VALUES('LJB', '임재범', 1963, '서울', '016', '66666666', 182, '2009-9-9');
INSERT INTO userTbl VALUES('YJS', '윤종신', 1960, '경남', NULL, NULL, 170, '2005-5-5');
INSERT INTO userTbl VALUES('EJW', '은지원', 1972, '경북', '011', '88888888', 174, '2014-3-3');
INSERT INTO userTbl VALUES('JKW', '조관우', 1965, '경기', '016', '99999999', 172, '2010-10-10');
INSERT INTO userTbl VALUES('BBK', '바비킴', 1973, '서울', '010', '00000000', 176, '2013-5-5');
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('KBS', '운동화', '의류', 30, 2);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('KBS', '노트북', '전자', 1000, 1);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('JYP', '모니터', '전자', 200, 1);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('BBK', '모니터', '전자', 200, 5);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('KBS', '청바지', '의류', 50, 3);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('BBK', '메모리', '전자', 80, 10);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('SSK', '책', '서적', 15, 5);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('EJW', '책', '서적', 15, 2);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('EJW', '청바지', '의류', 50, 1);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('BBK', '운동화', '의류', 30, 2);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('EJW', '책', '서적', 15, 1);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('BBK', '운동화', '의류', 30, 2);
"""
cursor.execute(SQL_QUERY)
db.commit()
db.close()

실행환경이 구축되었으면 해당 데이터베이스로 접속해서 확인

host_name = "funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com"
username = "davelee"
password = "korea234"
database_name = "sqlDB"    # 데이터베이스 이름을 sakila 로 바꿔줘야 합니다.
db = pymysql.connect(
    host=host_name,  # DATABASE_HOST
    port=3306,
    user=username,  # DATABASE_USERNAME
    passwd=password,  # DATABASE_PASSWORD
    db=database_name,  # DATABASE_NAME
    charset='utf8'
)
SQL = "select * from userTbl"
df = pd.read_sql(SQL, db)
df
Out[25]:
userID name birthYear addr mobile1 mobile2 height mDate
0 BBK 바비킴 1973 서울 010 00000000 176 2013-05-05
1 EJW 은지원 1972 경북 011 88888888 174 2014-03-03
2 JKW 조관우 1965 경기 016 99999999 172 2010-10-10
3 JYP 조용필 1950 경기 011 44444444 166 2009-04-04
4 KBS 김범수 1979 경남 011 22222222 173 2012-04-04
5 KKH 김경호 1971 전남 019 33333333 177 2007-07-07
6 LJB 임재범 1963 서울 016 66666666 182 2009-09-09
7 LSG 이승기 1987 서울 011 11111111 182 2008-08-08
8 SSK 성시경 1979 서울 None None 186 2013-12-12
9 YJS 윤종신 1960 경남 None None 170 2005-05-05
SQL = "select * from buyTbl"
df = pd.read_sql(SQL, db)
df
Out[26]:
num userID prodName groupName price amount
0 1 KBS 운동화 의류 30 2
1 2 KBS 노트북 전자 1000 1
2 3 JYP 모니터 전자 200 1
3 4 BBK 모니터 전자 200 5
4 5 KBS 청바지 의류 50 3
5 6 BBK 메모리 전자 80 10
6 7 SSK 서적 15 5
7 8 EJW 서적 15 2
8 9 EJW 청바지 의류 50 1
9 10 BBK 운동화 의류 30 2
10 11 EJW 서적 15 1
11 12 BBK 운동화 의류 30 2

buyTbl에 데이터를 추가해본다.

cursor = db.cursor()
cursor.execute("set names utf8")
db.commit()
SQL_QUERY = "INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('STJ', '운동화', '의류', 30, 2);"
cursor.execute(SQL_QUERY)
db.commit()

에러가 나면 정상임

  • CONSTRAINT buyTbl_ibfk_1 FOREIGN KEY (userID) REFERENCES userTbl (userID)
  • userTbl 에 userID가 STJ인 데이터가 없기 때문에,
    • FOREIGN KEY (userID) REFERENCES userTbl(userID)
    • buyTbl 테이블의 userID 커럼은 userTbl 테이블의 userID를 참조할 때, userTbl 테이블에 userID가 STJ인 데이터가 없으면, 입력이 안됨
    • 데이터 무결성 (두 테이블간 관계에 있어서, 데이터의 정확성을 보장하는 제약 조건을 넣는 것임)
    • 현업에서는 꼭 필요한 경우만 사용하는 경우가 많음 (비즈니스 로직이 다양하기 때문에, 제약을 걸어놓을 경우, 예외적인 비즈니스 로직 처리가 어렵기 때문)

다음 데이터 넣어보기 (실습)

SQL_QUERY = "INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('BBK', '운동화', '의류', 30, 2);"
cursor.execute(SQL_QUERY)
db.commit()

에러나면, 가능하면 한번 db connection 닫아주시고, 다시 connection 해주는 편이 좋을 수 있습니다.

db.close()
host_name = "funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com"
username = "davelee"
password = "korea234"
database_name = "sqlDB"    # 데이터베이스 이름을 sakila 로 바꿔줘야 합니다.
db = pymysql.connect(
    host=host_name,  # DATABASE_HOST
    port=3306,
    user=username,  # DATABASE_USERNAME
    passwd=password,  # DATABASE_PASSWORD
    db=database_name,  # DATABASE_NAME
    charset='utf8'
)

이번에는 userTbl 에 userID가 STJ 인 데이터를 넣어준 후에, 다시 buyTbl userID에 STJ 관련 데이터를 넣어줍니다.

cursor = db.cursor()
cursor.execute("set names utf8")
db.commit()
SQL_QUERY = "INSERT INTO userTbl VALUES('STJ', '서태지', 1975, '경기', '011', '00000000', 171, '2014-4-4');"
cursor.execute(SQL_QUERY)
db.commit()
SQL_QUERY = "INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('STJ', '운동화', '의류', 30, 2);"
cursor.execute(SQL_QUERY)
db.commit()

이번에는 userTbl에 userID가 STJ 관련 데이터를 삭제해봅니다.

SQL_QUERY = "DELETE FROM userTbl WHERE userID = 'STJ'"
cursor.execute(SQL_QUERY)
db.commit()

에러나면 정상입니다.

  • buyTbl 에 해당 userID를 참조하는 데이터가 있기 때문입니다.