외래키 제약 조건은 관련 데이터의 일관성을 유지하는 데 사용합니다.

<aside>

💡 다른 종류의 제약 조건과 마찬가지로 데이터가 일치하지 않을 경우 데이터가 삽입되거나 업데이트되는 것을 방지할 수 있습니다. …… 또는 DML 작업이 수행될 때 제약 조건으로 인해 하위 행 의 데이터가 삭제되거나, 다른 값으로 변경되거나, 외래 키를 생성할 때 지정된 옵션 에 따라 null 로 설정될 수 있습니다. 참고링크

</aside>

외래키

먼저 공식 문서를 읽어보면…
- 외래 키는 관련 정보의 빠른 조회를 가능하게 하는 것 외에도 데이터가 삽입, 업데이트 및 삭제될 때 이러한 포인터가 무효화되는 것을 방지하여 참조 무결성을 강화하는 데 도움이 됩니다. 이 강제 메커니즘은 제약 조건 의 한 유형입니다.
- 연결된 외래 키 값이 다른 테이블에 없으면 다른 테이블을 가리키는 행을 삽입할 수 없습니다.
- 행이 삭제되거나 외래 키 값이 변경되고 다른 테이블의 행이 해당 외래 키 값을 가리키는 경우 삭제를 방지하도록 외래 키를 설정하여 다른 테이블의 해당 열 값이 null이 되도록 할 수 있거나 다른 테이블에서 해당 행을 자동으로 삭제합니다.
- 정규화된 데이터베이스를 설계하는 단계 중 하나 는 조인 작업을 사용하여 중복된 데이터를 식별하고 해당 데이터를 새 테이블로 분리하고 외래 키 관계를 설정하여 여러 테이블을 단일 테이블처럼 쿼리할 수 있도록 하는 것입니다 .
지금 있는 외래키를 확인할 수 있는 방법

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;

외래키 제약 조건

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option] -- Cascade, Set null, Set default, Restrict, No Action(default, Restrict와 동일)
    [ON UPDATE reference_option] -- Cascade, Set null, Set default

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

상위 및 하위 테이블은 동일한 스토리지 엔진을 사용해야 합니다. 임시 테이블로 정의할 수 없습니다.
- 임시테이블 : 단일 세션에서 여러 번 재사용할 수 있는 임시 결과 세트를 저장할 수 있는 특수한 유형의 테이블 참고링크

외래키 제약 조건의 생성

외래 키 제약 조건을 만들려면
- [REFERENCES](<https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_references>)상위 테이블(부모 테이블)에 대한 권한이 필요합니다.
외래 키와 참조 키의 해당 열은 유사한 데이터 유형을 가져야 합니다.
- 문자열 유형의 길이는 같을 필요가 없습니다.
- 이진이 아닌(문자) 문자열 열의 경우 문자 집합과 데이터 정렬이 동일해야 합니다.
외래키와 인덱스
- 기본 키 제약 조건과 달리 외래 키 제약 조건을 만들어도 해당 인덱스가 자동으로 생성되지 않습니다.
- 쿼리 최적화를 위해 인덱스를 만드는 것을 추천합니다.
- (MySQL은 자동 생성됩니다) MySQL은 외래 키 검사가 빠르고 테이블 스캔이 필요하지 않도록 외래 키 및 참조 키에 대한 인덱스가 필요합니다.
  - 참조 테이블에는 외래 키 열이 동일한 순서로 첫 번째 열로 나열되는 인덱스가 있어야 합니다.
  - 이러한 인덱스는 존재하지 않는 경우 참조하는 테이블에 자동으로 생성됩니다. 외래 키 제약 조건을 적용하는 데 사용할 수 있는 다른 인덱스를 만들면 나중에 이 인덱스가 자동으로 삭제될 수 있습니다.
외래 키 관계의 테이블은 다른 스토리지 엔진을 사용하도록 변경할 수 없습니다. 스토리지 엔진을 변경하려면 먼저 외래 키 제약 조건을 삭제해야 합니다.

-- 생성할 때 외래키도 같이 생성
CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

-- 수정할 때 외래키 생성
ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

외래키 삭제

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

외래키 제약 조건 체크

MySQL에서 InnoDB는 외래키 제약 조건 검사를 지원합니다.
기본적으로 활성화되어있는 FOREIGN_KEY_CHECKS 변수에 의해 제어됩니다.

SET FOREIGN_KEY_CHECKS=0; // 끄기
SET FOREIGN_KEY_CHECKS=1; // 켜기

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

써보신 적이 있나요?
보통은 이런 에러에서 자식 테이블 값을 수정하고 다시 제약 조건을 켜는 용도로 많이 사용합니다..
혹은 데이터를 dump 할 때 비활성화합니다. mysqldump는 자동으로 외래키 제약조건을 끄는 쿼리가 포함되어 있습니다.
foreign_key_checks를 비활성화하면 외래 키 검사를 피할 수 있어 가져오기 작업 속도가 빨라집니다.
주의사항
- 1 로 값을 바꾸더라도 이전에 있었던 테이블 데이터에 대한 스캔을 실행하지 않습니다.
  - 즉, 0 으로 설정되어있을 때 추가된 테이블에 대해서도 검증하지 않습니다.
- 외래키 제약 조건에 필요한 index 를 삭제하는 작업은 해당 설정의 설정값과 상관 없이 허용되지 않습니다.

외래키와 참조 무결성

이 제약 조건은 기본 키 테이블의 데이터를 변경할 때 외래 키 테이블에 있는 데이터로의 연결이 무효화될 가능성이 있으면 그 데이터를 변경하지 못하도록 하여 참조 무결성을 강제 적용합니다.
- 삭제되거나 변경되는 기본 키 값이 다른 테이블의 외래키로 참조되고 있다면, 동작이 수행되지 않을 수도 있습니다.
이를 지키기 위해 참조하는 키가 삭제 혹은 업데이트 하려고 할 때 데이터베이스 엔진이 수행할 동작을 지정할 수 있습니다.
- NO ACTION 부모 테이블에 행에 대한 삭제 또는 업데이트 동작이 롤백된다.
- SET NULL 해당 외래키를 구성하는 모든 값이 null 이 된다.
- CASCADE 삭제 전파. 만약 참조되는 키의 일부인 경우(다른 곳에서도 참조되고 있는 경우)에는 CASCADE를 지정할 수 없다. 부모 테이블에서 자식의 자식 테이블까지 계단식으로 작동할 수 있다. 다만, 계단식 외래키 작업으로 트리거가 실행되지는 않는다. 참고링크
- SET DEFAULT 기본값으로 설정
- RESTRICT 상위 테이블에 대한 삭제 또는 업데이트 작업을 아예 막는다.

create table if not exists chat_log
(
    id           bigint auto_increment
        primary key,
    contents     varchar(300) null,
    sender_id    bigint       not null,
    reciver_id   bigint       not null,
    chat_room_id bigint       not null,
    constraint fk_chat_log_member1
        foreign key (sender_id) references member (id),
    constraint fk_chat_log_member2
        foreign key (reciver_id) references member (id),
    constraint fk_chat_log_member_chat_about_item1
        foreign key (chat_room_id) references chatroom (id)
		**on delete cascade** // 삭제 전파
);

외래키 제약조건이 켜져있으면 성능 저하를 유발하는 것들

DDL

전반적으로 데이터의 양이 더 많아지고 복잡해집니다.
참조되는 릴레이션이 선행되어야 합니다. 이것은 DDL 뿐만 아니라 레코드를 생성, 수정할 때마다 순서를 신경써줘야합니다.

DML

INSERT, UPDATE, DELETE 모두 적용됩니다…

CASCADE 옵션이 있다면 관련된 작업들을 처리해야합니다.
- 참조하는 릴레이션의 데이터가 모두 변경됩니다.
데이터 정합성을 위해 외래키 제약 조건을 만족하는지 확인합니다.
MySQL은 상위 테이블에 일치하는 후보 키 값이 없으면 하위 테이블에 외래 키 값을 생성하려는 작업을 거부합니다.

Lock

MySQL은 필요에 따라 외래키 제약 조건과 관련된 테이블까지 잠금을 확장합니다.
- DML 및 DDL 작업이 관련 테이블에서 동시에 실행되는 것을 방지하기 위해서입니다.
- 부모 테이블의 메타데이터가 업데이트 될 때 외래키 메타데이터 업데이트까지 전파할 수 잇게 됩니다.
  - MySQL 8.0에서는 자식 테이블을 통해서도 외래키 메타데이터를 업데이트 할 수 있습니다.
테이블이 LOCK_TABLES 로 명시적으로 잠길 때, 외래키 제약 조건에 의해 관련된 모든 테이블이 암시적으로 같이 잠깁니다.
- 외래키 검사를 할 때 테이블에 대해 shared read-only lock이 걸립니다. LOCK_TABLES_READ 에 추가됩니다.
- 계단식 업데이트를 할 때는 shared-nothing write lock이 걸립니다. LOCK_TABLES_WRTIE 에 추가됩니다.
이렇게 같이 lock되기 때문에, 외래키로 성능 저하가 일어날 수 있습니다.

Refs.

MySQL :: MySQL 8.0 Reference Manual :: 13.1.20.5 FOREIGN KEY Constraints

MySQL :: MySQL 8.0 Reference Manual :: 8.3.4 Foreign Key Optimization

Can foreign keys hurt query performance

저작자표시 비영리 동일조건

MySQL 에서 외래키가 있으면 어떤 것들을 검증할까?

외래키

외래키 제약 조건

외래키 제약 조건의 생성

외래키 제약 조건 체크

외래키와 참조 무결성

외래키 제약조건이 켜져있으면 성능 저하를 유발하는 것들

DDL

DML

Lock

Refs.

티스토리툴바