데이터베이스는 여러 개의 테이블로 구성되기 마련이다. 이들 테이블은 특정 키(혹은 필드)를 매개로 서로 연결이 되는데, 이 키를 외래키(foreign key)라고 한다. RDBMS(Relational database management system)에서 명시적으로 테이블을 연결(relation)해주는 장치다. 아래는 joinc 사이트의 유저 관리 테이블이다.
이 3개의 테이블은 user.id로 연결된다. preference 테이블과 bookmark 테이블의 userid가 외래키이다. 다른 말로 이 두 테이블은 userid를 키로 user.id를 참조한다고 할 수 있다.
기본키 : 외래키가 참조하는 키. 이 경우 user.id가 기본키다.
외래키 : 기본키를 참조하는 키
Child 테이블 : 외래키를 가지고 있는 테이블
user.id 값이 없는데, perference, bookmark에 레코드가 들어가면 안된다는 제약조건이 걸린다. 여러 이유로 존재하지 않는 user.id를 key로 해서 INSERT를 할 경우 에러를 반환한다. 반대로 user.id를 삭제하려 할 때도, 이 값을 외래키로 참조하는 테이블이 있다면 에러를 반환한다.
이러한 일들은 외래키를 설정하지 않고, 애플리케이션에서 몇 개의 쿼리를 조합하는 것만으로도 수행 할 수 있다. 유저가 서비스에서 탈퇴하면 user 테이블에서 레코드를 삭제해야 할 건데, preference와 bookmark에를 삭제하는 쿼리를 만들어서 수행해되 된다. 하지만 개발자가 하는 일이니 만큼 실수가 있을 수 있어서 데이터 정합성이 깨질수 있다. 시스템에 맡길 수 있는 건 맡기는게 좋겠다.
테스트
유저 주문을 기록하는 간단한 애플리케이션을 만들기 위해서 두 개의 테이블을 준비했다. 테이블 테스트를 위해서 test 데이터베이스를 만들었다.
CREATE TABLE Persons (
PersonID int NOT NULL AUTO_INCREMENT,
Name VARCHAR(80) NOT NULL,
PRIMARY KEY (PersonID)
);
CREATE TABLE Orders (
OrderID int NOT NULL AUTO_INCREMENT,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
Persons에 유저 정보가 저장된다. PersonID를 Primary key로 사용한다. Orders에 유저의 주문 정보를 저장한다. Persons 테이블의 PersonID를 외래키로 등록 두 개의 테이블을 연결했다.
INSERT INTO Persons SET Name='yundream';
INSERT INTO Persons SET Name='hong.kil.dong';
INSERT INTO Persons SET Name='kim.harry';
Persons 테이블에 존재하지 않는 PersonID로 Insert를 하면 제약조건 위반 에러가 발생한다.
mysql> INSERT INTO Orders SET OrderID=4, OrderNumber=100, PersonID=5;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails \
(`test`.`Orders`, CONSTRAINT `Orders_ibfk_1` FOREIGN KEY (`PersonID`) REFERENCES `Persons` (`PersonID`))
제약조건을 만족하는 3개의 Orders 아이템을 Insert 했다.
INSERT INTO Orders SET OrderNumber=5001, PersonID=1;
INSERT INTO Orders SET OrderNumber=5124, PersonID=1;
INSERT INTO Orders SET OrderNumber=5001, PersonID=3;
mysql> DELETE FROM Persons WHERE PersonID = 3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails \
(`test`.`Orders`, CONSTRAINT `Orders_ibfk_1` FOREIGN KEY (`PersonID`) REFERENCES `Persons` (`PersonID`))
제약조건을 위반하기 때문에 실패한다. 외래키가 참조하고 있는 기본 키(PersonID = 3)을 삭제해야 유저를 삭제 할 수 있다. 주문이력이 남아있는 상태에서 유저를 삭제하면 안되므로 이러한 행동은 올바르다고 볼 수 있다.
기본키를 삭제할 때, 해당 기본키를 외래키로 하는 모든 값이 삭제되야 하는 경우도 있을 것이다. 그냥 DELETE를 이용해서 삭제해도 되겠지만, 테이블 구조가 복잡할 경우 까다로운 작업이 될 수 있다.
MySQL은 단일 DELETE 쿼리에 대해서 하위 테이블의 데이터를 자동으로 삭제 할 수 있도록 외래키에 대해서 ON DELETE CASCADE설정을 할 수 있다.
CREATE TABLE Orders (
OrderID int NOT NULL AUTO_INCREMENT,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
ON DELETE CASCADE
);
Persons에서 PersonID가 1인 레코드를 지워보자.
CREATE TABLE Persons (
PersonID int NOT NULL AUTO_INCREMENT,
Name VARCHAR(80) NOT NULL,
PRIMARY KEY (PersonID)
);
ON DELETE CASCADE 가 영향을 주는 테이블 찾기
ON DELETE CASCADE는 편하기는 하지만 조심해서 사용해야 한다. 테이블이 많고, 복잡하게 얽혀 있다면 조심해야 한다. 삭제하기 전에 ON DELETE CASCADE가 영향을 주는 테이블 목록을 확인하는 법을 알아보자.
USE information_schema;
SELECT
table_name
FROM
referential_constraints
WHERE
constraint_schema = 'test'
AND referenced_table_name = 'Persons'
AND delete_rule = 'CASCADE'
아래와 같은 결과를 확인 할 수 있었다.
+------------+
| table_name |
+------------+
| Orders |
+------------+
1 row in set (0.00 sec)
Contents
MySQL 외래키
- 기본키 : 외래키가 참조하는 키. 이 경우 user.id가 기본키다.
- 외래키 : 기본키를 참조하는 키
- Child 테이블 : 외래키를 가지고 있는 테이블
user.id 값이 없는데, perference, bookmark에 레코드가 들어가면 안된다는 제약조건이 걸린다. 여러 이유로 존재하지 않는 user.id를 key로 해서 INSERT를 할 경우 에러를 반환한다. 반대로 user.id를 삭제하려 할 때도, 이 값을 외래키로 참조하는 테이블이 있다면 에러를 반환한다. 이러한 일들은 외래키를 설정하지 않고, 애플리케이션에서 몇 개의 쿼리를 조합하는 것만으로도 수행 할 수 있다. 유저가 서비스에서 탈퇴하면 user 테이블에서 레코드를 삭제해야 할 건데, preference와 bookmark에를 삭제하는 쿼리를 만들어서 수행해되 된다. 하지만 개발자가 하는 일이니 만큼 실수가 있을 수 있어서 데이터 정합성이 깨질수 있다. 시스템에 맡길 수 있는 건 맡기는게 좋겠다.테스트
CREATE TABLE Persons ( PersonID int NOT NULL AUTO_INCREMENT, Name VARCHAR(80) NOT NULL, PRIMARY KEY (PersonID) ); CREATE TABLE Orders ( OrderID int NOT NULL AUTO_INCREMENT, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) );CREATE TABLE Orders ( OrderID int NOT NULL AUTO_INCREMENT, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) ON DELETE CASCADE );CREATE TABLE Persons ( PersonID int NOT NULL AUTO_INCREMENT, Name VARCHAR(80) NOT NULL, PRIMARY KEY (PersonID) );ON DELETE CASCADE 가 영향을 주는 테이블 찾기
USE information_schema; SELECT table_name FROM referential_constraints WHERE constraint_schema = 'test' AND referenced_table_name = 'Persons' AND delete_rule = 'CASCADE'참고
Recent Posts
Archive Posts
Tags