5.6.6 Using Foreign Keys

5.6.6 Using Foreign Keys

MySQL은 외래 키를 지원합니다. 외래 키는 테이블 간에 관련된 데이터를 교차 참조할 수 있게 해 주며, 외래 키 제약 조건은 관련된 데이터가 일관성을 유지하도록 도와줍니다.

외래 키 관계에는 초기 열 값을 보유하는 부모 테이블과, 부모 열 값을 참조하는 열 값을 가진 자식 테이블이 포함됩니다. 외래 키 제약 조건은 자식 테이블에 정의됩니다.

다음 예제는 parent 테이블과 child 테이블을 단일 열 외래 키로 연결하고, 외래 키 제약 조건이 어떻게 참조 무결성을 강제하는지 보여 줍니다.

다음 SQL 문을 사용하여 parent 테이블과 child 테이블을 생성합니다:

1CREATE TABLE parent (
2    id INT NOT NULL,
3    PRIMARY KEY (id)
4) ENGINE=INNODB;
5
6CREATE TABLE child (
7    id INT,
8    parent_id INT,
9    INDEX par_ind (parent_id),
10    FOREIGN KEY (parent_id)
11        REFERENCES parent(id)
12) ENGINE=INNODB;

다음과 같이 parent 테이블에 행을 하나 삽입합니다:

1mysql> INSERT INTO parent (id) VALUES ROW(1);

데이터가 삽입되었는지 확인합니다. 이를 위해 아래와 같이 단순히 parent의 모든 행을 조회할 수 있습니다:

1mysql> TABLE parent;
2+----+
3| id |
4+----+
5|  1 |
6+----+

다음 SQL 문을 사용하여 child 테이블에 행을 하나 삽입합니다:

1mysql> INSERT INTO child (id,parent_id) VALUES ROW(1,1);

parent_id 1이 parent 테이블에 존재하므로 삽입 연산은 성공합니다.

parent 테이블에 존재하지 않는 parent_id 값을 가진 행을 child 테이블에 삽입하려는 시도는, 아래와 같이 오류와 함께 거부됩니다:

1mysql> INSERT INTO child (id,parent_id) VALUES ROW(2,2);
2ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
3(`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
4REFERENCES `parent` (`id`))

지정한 parent_id 값이 parent 테이블에 존재하지 않기 때문에 연산이 실패합니다.

이전에 삽입한 행을 parent 테이블에서 삭제하려는 시도 역시 아래와 같이 실패합니다:

1mysql> DELETE FROM parent WHERE id VALUES = 1;
2ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
3(`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
4REFERENCES `parent` (`id`))

이 연산은 child 테이블의 레코드가 참조된 id (parent_id) 값을 포함하고 있기 때문에 실패합니다.

parent 테이블에서 키 값을 변경하는 연산이 child 테이블의 일치하는 행에 영향을 미치는 경우, 결과는 FOREIGN KEY 절의 ON UPDATE 및 ON DELETE 하위 절에 의해 지정된 참조 동작에 따라 달라집니다. (현재 child 테이블 정의에서와 같이) ON DELETE 및 ON UPDATE 절을 생략하는 것은 parent 테이블에서 일치하는 행을 가진 키 값을 변경하는 연산을 거부하는 RESTRICT 옵션을 지정하는 것과 동일합니다. 이 경우 parent 테이블에 일치하는 행을 가진 키 값에 영향을 미치는 연산은 거부됩니다.

ON DELETE 및 ON UPDATE 참조 동작을 보여 주기 위해, child 테이블을 drop한 후 ON UPDATE 및 ON DELETE 하위 절에 CASCADE 옵션을 포함하도록 다시 생성합니다. CASCADE 옵션은 parent 테이블의 행을 삭제하거나 업데이트할 때 child 테이블의 일치하는 행을 자동으로 삭제하거나 업데이트합니다.

1DROP TABLE child;
2
3CREATE TABLE child (
4    id INT,
5    parent_id INT,
6    INDEX par_ind (parent_id),
7    FOREIGN KEY (parent_id)
8        REFERENCES parent(id)
9        ON UPDATE CASCADE
10        ON DELETE CASCADE
11) ENGINE=INNODB;

아래 문을 사용하여 child 테이블에 행을 몇 개 삽입합니다:

1mysql> INSERT INTO child (id,parent_id) VALUES ROW(1,1), ROW(2,1), ROW(3,1);

다음과 같이 데이터가 삽입되었는지 확인합니다:

1mysql> TABLE child;
2+------+-----------+
3| id   | parent_id |
4+------+-----------+
5|    1 |         1 |
6|    2 |         1 |
7|    3 |         1 |
8+------+-----------+

다음 SQL 문을 사용하여 parent 테이블의 ID를 1에서 2로 변경합니다:

1mysql> UPDATE parent SET id = 2 WHERE id = 1;

아래와 같이 parent 테이블의 모든 행을 조회하여 업데이트가 성공했는지 확인합니다:

1mysql> TABLE parent;
2+----+
3| id |
4+----+
5|  2 |
6+----+

다음과 같이 ON UPDATE CASCADE 참조 동작이 child 테이블을 업데이트했는지 확인합니다:

1mysql> TABLE child;
2+------+-----------+
3| id   | parent_id |
4+------+-----------+
5|    1 |         2 |
6|    2 |         2 |
7|    3 |         2 |
8+------+-----------+

ON DELETE CASCADE 참조 동작을 보여 주기 위해, parent 테이블에서 parent_id = 2인 레코드를 삭제합니다. 이는 parent 테이블의 모든 레코드를 삭제합니다.

1mysql> DELETE FROM parent WHERE id = 2;

child 테이블의 모든 레코드가 parent_id = 2와 연관되어 있으므로, ON DELETE CASCADE 참조 동작은 아래와 같이 child 테이블의 모든 레코드를 제거합니다:

1mysql> TABLE child;
2Empty set (0.00 sec)

외래 키 제약 조건에 대한 자세한 내용은 Section 15.1.24.5, “FOREIGN KEY Constraints”를 참조하십시오.