Mysql 03 FOREIGN KEY

Q·2021년 7월 20일
0

MySQL

목록 보기
4/5

외래키 (FOREIGN KEY)

데이터베이스 준비

실습 환경 구축

  • userbuy.sql 을 Workbench 로 실행시키기

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

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

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

  • 두 테이블 사이에 관계를 선언해서, 데이터의 무결성을 보장

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

import pymysql
import pandas as pd
host_name = 'localhost'
host_port = 3306
username = 'root'
password = '1234'
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)
df
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

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

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

IntegrityError                            Traceback (most recent call last)

<ipython-input-6-30366af6fe2b> in <module>
      1 cursor = db.cursor()
      2 SQL_QUERY = "INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('STJ', '운동화', '의류', 30, 2);"
----> 3 cursor.execute(SQL_QUERY)
      4 db.commit()


~\Anaconda3\lib\site-packages\pymysql\cursors.py in execute(self, query, args)
    146         query = self.mogrify(query, args)
    147 
--> 148         result = self._query(query)
    149         self._executed = query
    150         return result


~\Anaconda3\lib\site-packages\pymysql\cursors.py in _query(self, q)
    308         self._last_executed = q
    309         self._clear_result()
--> 310         conn.query(q)
    311         self._do_get_result()
    312         return self.rowcount


~\Anaconda3\lib\site-packages\pymysql\connections.py in query(self, sql, unbuffered)
    546             sql = sql.encode(self.encoding, "surrogateescape")
    547         self._execute_command(COMMAND.COM_QUERY, sql)
--> 548         self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    549         return self._affected_rows
    550 


~\Anaconda3\lib\site-packages\pymysql\connections.py in _read_query_result(self, unbuffered)
    773         else:
    774             result = MySQLResult(self)
--> 775             result.read()
    776         self._result = result
    777         if result.server_status is not None:


~\Anaconda3\lib\site-packages\pymysql\connections.py in read(self)
   1154     def read(self):
   1155         try:
-> 1156             first_packet = self.connection._read_packet()
   1157 
   1158             if first_packet.is_ok_packet():


~\Anaconda3\lib\site-packages\pymysql\connections.py in _read_packet(self, packet_type)
    723             if self._result is not None and self._result.unbuffered_active is True:
    724                 self._result.unbuffered_active = False
--> 725             packet.raise_for_error()
    726         return packet
    727 


~\Anaconda3\lib\site-packages\pymysql\protocol.py in raise_for_error(self)
    219         if DEBUG:
    220             print("errno =", errno)
--> 221         err.raise_mysql_exception(self._data)
    222 
    223     def dump(self):


~\Anaconda3\lib\site-packages\pymysql\err.py in raise_mysql_exception(data)
    141     if errorclass is None:
    142         errorclass = InternalError if errno < 1000 else OperationalError
--> 143     raise errorclass(errno, errval)


IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (`sqldb`.`buytbl`, CONSTRAINT `buytbl_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `usertbl` (`userID`))')

에러가 나면 정상임

  • 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인 데이터가 없으면, 입력이 안됨
    • 데이터 무결성 (두 테이블간 관계에 있어서, 데이터의 정확성을 보장하는 제약 조건을 넣는 것임)

다음 데이터 넣어보기

cursor = db.cursor()
SQL_QUERY = "INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('BBK', '운동화', '의류', 30, 2);"
cursor.execute(SQL_QUERY)
db.commit()
db.close()
import pymysql
import pandas as pd
host_name = 'localhost'
host_port = 3306
username = 'root'
password = '1234'
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 에 userID가 STJ 인 데이터를 넣어준 후에, 다시 buyTbl userID에 STJ 관련 데이터를 넣어줍니다.

cursor = db.cursor()
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()
---------------------------------------------------------------------------

IntegrityError                            Traceback (most recent call last)

<ipython-input-14-c069f514884e> in <module>
      1 SQL_QUERY = "DELETE FROM userTbl WHERE userID = 'STJ'"
----> 2 cursor.execute(SQL_QUERY)
      3 db.commit()


~\Anaconda3\lib\site-packages\pymysql\cursors.py in execute(self, query, args)
    146         query = self.mogrify(query, args)
    147 
--> 148         result = self._query(query)
    149         self._executed = query
    150         return result


~\Anaconda3\lib\site-packages\pymysql\cursors.py in _query(self, q)
    308         self._last_executed = q
    309         self._clear_result()
--> 310         conn.query(q)
    311         self._do_get_result()
    312         return self.rowcount


~\Anaconda3\lib\site-packages\pymysql\connections.py in query(self, sql, unbuffered)
    546             sql = sql.encode(self.encoding, "surrogateescape")
    547         self._execute_command(COMMAND.COM_QUERY, sql)
--> 548         self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    549         return self._affected_rows
    550 


~\Anaconda3\lib\site-packages\pymysql\connections.py in _read_query_result(self, unbuffered)
    773         else:
    774             result = MySQLResult(self)
--> 775             result.read()
    776         self._result = result
    777         if result.server_status is not None:


~\Anaconda3\lib\site-packages\pymysql\connections.py in read(self)
   1154     def read(self):
   1155         try:
-> 1156             first_packet = self.connection._read_packet()
   1157 
   1158             if first_packet.is_ok_packet():


~\Anaconda3\lib\site-packages\pymysql\connections.py in _read_packet(self, packet_type)
    723             if self._result is not None and self._result.unbuffered_active is True:
    724                 self._result.unbuffered_active = False
--> 725             packet.raise_for_error()
    726         return packet
    727 


~\Anaconda3\lib\site-packages\pymysql\protocol.py in raise_for_error(self)
    219         if DEBUG:
    220             print("errno =", errno)
--> 221         err.raise_mysql_exception(self._data)
    222 
    223     def dump(self):


~\Anaconda3\lib\site-packages\pymysql\err.py in raise_mysql_exception(data)
    141     if errorclass is None:
    142         errorclass = InternalError if errno < 1000 else OperationalError
--> 143     raise errorclass(errno, errval)


IntegrityError: (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`sqldb`.`buytbl`, CONSTRAINT `buytbl_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `usertbl` (`userID`))')

에러나면 정상

  • buyTbl 에 해당 userID를 참조하는 데이터가 있기 때문
profile
Q
Data Engineer
이전 포스트

Mysql 02_1 pandas

다음 포스트

Mysql 대용량 데이터 Update

0개의 댓글