외래키 (FOREIGN KEY)

이해하기 쉽고, 장황하지 않은 자료를 기반으로 강의를 진행합니다.

잔재미코딩 소식 공유

좀더 제약없이, IT 컨텐츠를 공유하고자, 자체 온라인 강의 사이트와 유투브 채널을 오픈하였습니다
응원해주시면, 곧 좋은 컨텐츠를 만들어서 공유하겠습니다

● 직운영 온라인 강의 서비스 오픈

● 잔재미코딩 유투브 오픈 [구독해보기]

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()

본 자료 보다 업데이트된 자료와 함께 온라인 강의로 익히면 체계적으로 이해할 수 있습니다!

퀄러티가 다른 온라인 강의로 차근차근 익혀보세요

SQL/DB(MySQL) 기본부터 파이썬/데이터 분석 활용 강의

SQL 과 데이터베이스, 그리고 파이썬으로 MySQL을 다루는 방법까지 쉽고 견고하게 정리하였습니다

자세히 알아보기 모든 강좌 보기

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);
"""

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다

데이터 분석/과학 로드맵

데이터 분석가와 데이터 과학자 직군의 기본기를 차근차근 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

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'
)

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

가장 빠르게 풀스택 개발자가 될 수 있도록, 최적화된 로드맵을 제공합니다

웹/앱서비스 풀스택 로드맵

자세히 알아보기 모든 강좌 보기

In [25]:

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

In [26]:

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에 데이터를 추가해본다.¶

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()

본 자료 보다 업데이트된 자료와 함께 온라인 강의로 익히면 체계적으로 이해할 수 있습니다!

퀄러티가 다른 온라인 강의로 차근차근 익혀보세요

SQL/DB(MySQL) 기본부터 파이썬/데이터 분석 활용 강의

SQL 과 데이터베이스, 그리고 파이썬으로 MySQL을 다루는 방법까지 쉽고 견고하게 정리하였습니다

자세히 알아보기 모든 강좌 보기

에러가 나면 정상임¶

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인 데이터가 없으면, 입력이 안됨
- 데이터 무결성 (두 테이블간 관계에 있어서, 데이터의 정확성을 보장하는 제약 조건을 넣는 것임)
- 현업에서는 꼭 필요한 경우만 사용하는 경우가 많음 (비즈니스 로직이 다양하기 때문에, 제약을 걸어놓을 경우, 예외적인 비즈니스 로직 처리가 어렵기 때문)

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

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()

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

체계적으로 전문가 레벨까지 익힐 수 있도록 온라인 강의 로드맵을 제공합니다

데이터 분석/과학 로드맵

데이터 분석가와 데이터 과학자 직군의 기본기를 차근차근 쌓을 수 있도록 꾸몄습니다

자세히 알아보기 모든 강좌 보기

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()

본 자료와 같이 IT 기술을 잘 정리하여, 온라인 강의로 제공하고 있습니다

가장 빠르게 풀스택 개발자가 될 수 있도록, 최적화된 로드맵을 제공합니다

웹/앱서비스 풀스택 로드맵

자세히 알아보기 모든 강좌 보기

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

In [ ]:

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

에러나면 정상입니다.¶

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