MySQL - Foreign Key

 

데이터베이스 입문 수업을 듣고 중요한 내용을 정리했습니다.
개인 공부 후 자료를 남기기 위한 목적이므로 내용 상에 오류가 있을 수 있습니다.

 

Foreign Key(MySQL)

Foreign Key는 두 테이블 사이에 관계를 선언해서, 데이터의 무결성을 보장하는 제약이다.

 

Foreign Key 실습환경 구축

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

 

sqlDB를 만들고, userTbl과 buyTbl의 두 테이블을 만들었다.

 

위의 예시코드에서 Foreign Key 관련 SQL 구문은 FOREIGN KEY (userID) REFERENCES userTbl(userID)이다.

이는 buyTbl 테이블의 userID 컬럼이 userTbl 테이블의 userID 컬럼을 Foreign Key로 참조한다는 것을 의미한다.

 

 

Foreign Key의 이해

import pymysql
import pandas as pd

host_name = 'localhost'
host_port = 3306
username = 'root'
password = 'mysql pw'
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

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

 

```

결과 :

userTbl

buyTbl

```

 

위에서 만든 실습환경을 Python 코드로 데이터베이스에 접속하여 확인한 것이다.

앞서 언급한 것처럼, buyTbl 테이블의 userID 컬럼이 userTbl 테이블의 userID 컬럼을 Foreign Key로 참조한다.

 

 

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

 

```

결과 :

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

 

```

 

위의 코드처럼, userTbl 테이블에 없는 데이터('STJ')를 buyTbl 테이블에 추가하면 에러가 발생한다.

그 이유는 buyTbl 테이블의 데이터가 userTbl 테이블의 데이터를 Foreign Key로 참조해야 하는데, STJ 데이터는

userTbl에 존재하지 않아 Foreign Key로 참조할 수 없기 때문이다. (데이터 무결성에 위배됨)

즉, STJ 데이터를 userTbl 테이블에 먼저 넣고 난 후 buyTbl 테이블에 동일한 데이터를 넣어야 에러가 발생하지 않는다.

 

*참고

데이터 무결성은 두 테이블간 관계에 있어서, 데이터의 정확성을 보장하는 제약조건을 넣는 것이다.

현업에서는 꼭 필요한 경우에만 사용하는 경우가 많으며, 비지니스 로직이 다양하기 때문에 제약을 걸어놓을 경우

예외적인 비지니스 로직 처리가 어렵게 된다.

 

 

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

 

```

결과 :

buyTbl

```

 

이 경우에는 userTbl 테이블에 BBK 데이터가 존재하기 때문에, buyTbl 테이블에 해당 데이터가 입력되는 것이다.

즉, Foreign Key는 다른 테이블과의 데이터 무결성을 점검하는 제약조건으로, 이를 충족해야만 데이터가 입력된다.

 

 

import pymysql
import pandas as pd

host_name = 'localhost'
host_port = 3306
username = 'root'
password = 'mysql pw'
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'
)

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

buyTbl

```

 

이제는 'STJ' 데이터가 userTbl 테이블에 존재하기 때문에, 해당 데이터를 buyTbl 테이블에 입력해도 에러가

발생하지 않는 것이다. (데이터 무결성 위배x)

 

 

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

 

```

결과 :

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

 

```

 

위의 예시코드처럼, userTbl 테이블의 데이터를 삭제할 경우에도 반드시 buyTbl 테이블에서 먼저 해당 데이터를

삭제하고 그 이후에 userTbl 테이블에서 해당 데이터를 삭제해야 한다. (데이터 입력과 반대 순서)

그 이유는 userTbl 테이블에서 특정 데이터를 삭제하면, buyTbl 테이블의 데이터는 더 이상 Foreign Key를 참조할 수

없기 때문이다. (데이터 무결성 위배)