외래키 (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 에서 실행하면 됨 (둘다 익숙해진다는 측면으로 해보세요)
In [11]:
db.close()
In [12]:
import requests
from bs4 import BeautifulSoup
import pandas as pd
In [13]:
host_name = "funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com"
username = "davelee"
password = "korea234"
database_name = "sakila" # 데이터베이스 이름을 sakila 로 바꿔줘야 합니다.
In [14]:
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'
)
In [15]:
cursor = db.cursor()
cursor.execute("set names utf8")
db.commit()
In [16]:
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);
"""
In [17]:
cursor.execute(SQL_QUERY)
db.commit()
In [22]:
db.close()
실행환경이 구축되었으면 해당 데이터베이스로 접속해서 확인¶
In [23]:
host_name = "funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com"
username = "davelee"
password = "korea234"
database_name = "sqlDB" # 데이터베이스 이름을 sakila 로 바꿔줘야 합니다.
In [24]:
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'
)
In [25]:
SQL = "select * from userTbl"
df = pd.read_sql(SQL, db)
df
Out[25]:
In [26]:
SQL = "select * from buyTbl"
df = pd.read_sql(SQL, db)
df
Out[26]:
buyTbl에 데이터를 추가해본다.¶
In [27]:
cursor = db.cursor()
cursor.execute("set names utf8")
db.commit()
In [ ]:
SQL_QUERY = "INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('STJ', '운동화', '의류', 30, 2);"
cursor.execute(SQL_QUERY)
db.commit()
에러가 나면 정상임¶
- CONSTRAINT
buyTbl_ibfk_1FOREIGN KEY (userID) REFERENCESuserTbl(userID) - userTbl 에 userID가 STJ인 데이터가 없기 때문에,
- FOREIGN KEY (userID) REFERENCES userTbl(userID)
- buyTbl 테이블의 userID 커럼은 userTbl 테이블의 userID를 참조할 때, userTbl 테이블에 userID가 STJ인 데이터가 없으면, 입력이 안됨
- 데이터 무결성 (두 테이블간 관계에 있어서, 데이터의 정확성을 보장하는 제약 조건을 넣는 것임)
- 현업에서는 꼭 필요한 경우만 사용하는 경우가 많음 (비즈니스 로직이 다양하기 때문에, 제약을 걸어놓을 경우, 예외적인 비즈니스 로직 처리가 어렵기 때문)
다음 데이터 넣어보기 (실습)¶
In [41]:
SQL_QUERY = "INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('BBK', '운동화', '의류', 30, 2);"
cursor.execute(SQL_QUERY)
db.commit()
에러나면, 가능하면 한번 db connection 닫아주시고, 다시 connection 해주는 편이 좋을 수 있습니다.¶
In [29]:
db.close()
In [30]:
host_name = "funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com"
username = "davelee"
password = "korea234"
database_name = "sqlDB" # 데이터베이스 이름을 sakila 로 바꿔줘야 합니다.
In [31]:
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 관련 데이터를 넣어줍니다.¶
In [32]:
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()
In [33]:
SQL_QUERY = "INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('STJ', '운동화', '의류', 30, 2);"
cursor.execute(SQL_QUERY)
db.commit()
이번에는 userTbl에 userID가 STJ 관련 데이터를 삭제해봅니다.¶
In [ ]:
SQL_QUERY = "DELETE FROM userTbl WHERE userID = 'STJ'"
cursor.execute(SQL_QUERY)
db.commit()
에러나면 정상입니다.¶
- buyTbl 에 해당 userID를 참조하는 데이터가 있기 때문입니다.