MySQL_Foreign key

Foreign Key(외래키)를 설정하는 이유

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

 

practice 라는 이름의 데이터베이스에 userTbl, buyTbl 이라는 두 테이블 생성

DROP DATABASE IF EXISTS practice;
CREATE DATABASE practice DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
USE practice;

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;

FOREIGN KEY (userID) REFERENCES userTbl(userID)
>> buyTbl 테이블의 userID 컬럼은 userTbl 테이블의 userID를 참조하고 있다는 의미

 

각 테이블에 데이터 추가

INSERT INTO userTbl VALUES('LSH', '이상혁', 1995, '서울', '011', '11111111', 182, '2008-8-8');
INSERT INTO userTbl VALUES('CWJ', '최우제', 2004, '경남', '011', '22222222', 173, '2012-4-4');
INSERT INTO userTbl VALUES('MHJ', '문현준', 2002, '전남', '019', '33333333', 177, '2007-7-7');
INSERT INTO userTbl VALUES('LMH', '이민형', 2002, '경기', '011', '44444444', 166, '2009-4-4');
INSERT INTO userTbl VALUES('RMS', '류민석', 2002, '서울', NULL, NULL, 186, '2013-12-12');

INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('LSH', '운동화', '의류', 30, 2);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('LSH', '노트북', '전자', 1000, 1);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('LSH', '모니터', '전자', 200, 1);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('CWJ', '모니터', '전자', 200, 5);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('LSH', '청바지', '의류', 50, 3);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('CWJ', '메모리', '전자', 80, 10);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('MHJ', '책', '서적', 15, 5);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('MHJ', '책', '서적', 15, 2);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('LMH', '청바지', '의류', 50, 1);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('LMH', '운동화', '의류', 30, 2);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('RMS', '책', '서적', 15, 1);
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('RMS', '운동화', '의류', 30, 2);

 

 

파이썬 환경에서 실행

import pymysql
import pandas as pd

host_name = 'localhost'
host_port = 3306
username = 'root'
password = '비밀번호'
database_name = 'practice'

db = pymysql.connect(
    host=host_name,
    port=host_port,
    user=username,
    passwd=password,
    db=database_name,
    charset='utf8'
)

 

usrTbl 확인

sql = "select * from userTbl"
df = pd.read_sql(sql, db)
df

buyTbl 확인

sql = "select * from buyTbl"
df = pd.read_sql(sql, db)
df

 

buyTbl에 데이터를 추가할 때,

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

• buyTbl 테이블의 userID 컬럼이 userTbl 테이블의 userID를 참조하고 있으므로,
  userTbl 테이블에 userID가 'KBH'라는 데이터가 없으면, 입력이 안됨
• 데이터 무결성을 체크하기 때문 (두 테이블간 관계에 있어서, 데이터의 정확성을 보장하는 제약 조건) 

userTbl 에 userID가 'KBH' 인 데이터를 넣어준 후에야,  buyTbl userID에 STJ 관련 데이터를 입력 가능하다.

 

 

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

userTbl에 userID가 'LSH'인 데이터를 삭제하려고 할 때, 에러가 발생한다.

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