01.Mysql 기초 - Foreign Key 다루기

ID짱재·2021년 2월 21일

mysql

MySQL

목록 보기

7/8

🌈 Foreign Key 실습

🔥 foreign key 만들기

🔥 pandas로 mysql 접속하기

🔥 foreign key 다루기

1. foreign key 지정하기

1) mysql workbench에서 sql파일로 테이블 2개 생성

foreign key를 만드는 이유는 두 테이블 사이에 관계를 선언해서 데이터의 무결성을 보장하기 위한 목적

table을 생성할 때 PRIMARY KEY 처럼 FOREIGN KEY를 미리 설정해두면 됨

FOREIGN KEY ([지정할 컬럼명]) REFERENCES [연결할table명]([연결할 컬럼명])

🔍 FOREIGN KEY userId REFERENCES userTbl(userId)

2) 실행 결과 확인하기

SELECT * FROM sqlDB.userTbl;

SELECT * FROM sqlDB.buyTbl;

2. pandas로 mysql 접속하기

1) pandas로 sqlDB접속 후 userTbl 테이블 읽어오기

read_sql(sql문, 데이터베이스 접속 정보) 메서드 사용

✍🏻 python

import pymysql
import pandas as pd
host_name = 'localhost'
host_port = 3306
username = 'root'
password = '비밀번호'
database_name = 'sqlDB'
db = pymysql.connect(
    host=host_name,     # MySQL Server Address
    port=host_port,     # MySQL Server Port
    user=username,      # MySQL username
    passwd=password,    # password for MySQL username
    db=database_name,   # Database name
    charset='utf8'
)
SQL = "select * from userTbl"
df = pd.read_sql(SQL, db)
print(df)

2) pandas로 sqlDB접속 후 buyTbl 테이블 읽어오기

✍🏻 python

import pymysql
import pandas as pd
host_name = 'localhost'
host_port = 3306
username = 'root'
password = '비밀번호'
database_name = 'sqlDB'
db = pymysql.connect(
    host=host_name,     # MySQL Server Address
    port=host_port,     # MySQL Server Port
    user=username,      # MySQL username
    passwd=password,    # password for MySQL username
    db=database_name,   # Database name
    charset='utf8'
)
SQL = "select * from buyTbl"
df = pd.read_sql(SQL, db)
print(df)

3. foreign key 다루기

buyTbl에 데이터 삽입할 때, 아래와 같이 sql 삽입문을 평소와 같이 사용하면 foreign key 때문에 오류 발생
✍🏻 python
cursor = db.cursor()
SQL_QUERY = "INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('STJ', '운동화', '의류', 30, 2);"
cursor.execute(SQL_QUERY)
db.commit()
이는 userTbl 테이블에 STJ인 데이터가 존재하지 않아 데이터 무결성에 문제가 생겼기 때문

이처럼 foreign key는 두 테이블간 관계에 있어서 데이터의 정확성을 보장하는 제약 조건인 데이터 무결성을 원칙으로 함

에러 발생 했을 때, db.close()를 통해 우선 db연결을 해제한 후 db를 다시 연결하여 조치

이에 userTbl 테이블에 먼저 데이터를 추가한 뒤, buyTbl에 데이터 삽입하면 됨
✍🏻 python

import pymysql
import pandas as pd
host_name = 'localhost'
host_port = 3306
username = 'root'
password = '비밀번호
database_name = 'sqlDB'
db = pymysql.connect(
    host=host_name,     # MySQL Server Address
    port=host_port,     # MySQL Server Port
    user=username,      # MySQL username
    passwd=password,    # password for MySQL username
    db=database_name,   # Database name
    charset='utf8'
)
# 데이터 무결성에 따른 문제를 해결하기 위해서 userTbl에 먼저 데이터를 추가한 뒤, buyTbl에 데이터를 추가
# userTbl에 데이터 추가(삽입)
cursor = db.cursor()
SQL_QUERY = "INSERT INTO userTbl VALUES('STJ', '서태지', 1975, '경기', '011', '00000000', 171, '2014-4-4');"
cursor.execute(SQL_QUERY)
db.commit()
# buyTbl에 데이터 추가(삽입)
SQL_QUERY = "INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('STJ', '운동화', '의류', 30, 2);"
cursor.execute(SQL_QUERY)
db.commit()

따라서 데이터 삽입은 참조되는 테이블부터 데이터를 삽입시킨 뒤, 참조를 하는(foreign key가 설정된) 테이블에 삽입을하는 순서로 DB를 다뤄야함
데이터 삭제는 이와 반대로, 참조를 하는(foreign key가 설정된) 테이블부터 삭제를 순차적으로 한 뒤, 참조받는 테이블의 데이터를 삭제 해야함