データベース3 FOREGN KEY

外部キー

データベースの準備

実験環境の構築

sqldb、usertbl、buytblの2つのテーブルの作成

外部キーを作成する理由

実行環境を構築したら、データベースに接続して検証してください。

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

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

間違いがあったら普通だ

次のデータを挿入してみます。

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

今回は、ユーザIDがSTJのデータをusertblに入れ、STJに関するデータをbuytbl userIDに入れる。

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

今回,user IDは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`))')

通常