포스팅 계기
프로젝트를 진행하면서 Foreign Key를 지정하고 연쇄 삭제를 진행하면서 아래의 에러가 발생했습니다.
DELETE FROM poll WHERE id = 1
Error Code: 1451. Cannot delete or update a parent
row: a foreign key constraint fails
(`tarotdb`.`result`, CONSTRAINT `poll_seq` FOREIGN KEY (`poll_id`)
REFERENCES `poll` (`id`)) 0.016 sec
요약하면 다음과 같습니다.
ERROR 1451 (23000):
Cannot delete or update a parent row: a foreign key constraint failsMySQL에서 FK(Foreign)로 설정되어 있는 row를 DELETE 하려고 할 때 나오는 에러메시지 입니다.
MySQL에 FK를 지정하고 삭제하는 경험을 해보지 않아서 처음 본 에러였고, 팀원들과 해당 에러를 파악하고 ON DELETE CASCADE를 FK 설정할 때 추가적으로 FOREIGN KEY CONSTRAINTS 옵션을 넣어줘야 한다는 것을 알게 되었습니다.
이번 포스팅은 FOREIGN KEY CONSTRAINTS에러를 해결하며 관련 옵션을 알아보기 위해 작성했습니다.
관련 공식문서는 아래 첨부하겠습니다.
MySQL 8.0 - Adding Foreign Key Constraints
참조 무결성
참조 무결성(Referential Integrity)은 데이터베이스 관리 시스템(DBMS)에서 데이터의 정확성과 일관성을 보장하는 개념입니다.
외래 키와 기본 키를 연결해서데이터 일관성을 유지하며,무효한 참조를 방지합니다.여기서 무효한 참조 방지란 외래 키가 존재하지 않거나 더 이상 유효하지 않은 기본 키를 참조하지 못하게 함으로써 데이터의 무결성을 유지하는 것을 의미합니다.
예를 들어
학생 Table이학교 Table의학교_ID를 외래 키로 참조한다고 가정해 보겠습니다.
학생 Table에 있는 모든학교_ID는
학교 Table에실제로 존재하는학교_ID와 일치해야 합니다.
학교 Table에서 어떤학교_ID를 삭제하려고 할 때, 해당 ID를 참조하는학생 Table의 행이 있다면, 삭제가 제한되거나, 해당 학생 테이블의 행에 추가적인 조치가 필요합니다.(예: 해당 행도 삭제, 외래 키 값을 NULL로 설정 등)즉, 학생은 실제로 존재하는 학교를 다녀야 하며,
학교는 학생들이 없어야 폐교(?)를 할 수 있습니다.
FK 제약 조건
참조 무결성을 유지하기 위해,
참조되는 테이블의 행이 삭제되거나 업데이트될 때,
참조하는 테이블의 행이 자동으로 삭제되거나 업데이트를 진행하게 된다면 참조 무결성이 유지되게 됩니다.참조 무결성을 유지하기 위해 MySQL에서 외래 키 제약 조건을 정의해서 사용합니다. 외래 키(Foreign Key)를 설정할 때
CASCADE옵션을 사용하고, 제약 조건 옵션들은RESTRICT,CASECADE,SET NULL이 있습니다
RESTRICE
부모 테이블의 특정 행이 자식 테이블에서 참조되고 있는 경우, 그 행의 삭제나 업데이트가 제한됩니다.
즉, 자식 테이블에 해당 부모 테이블의 행을 참조하는 데이터가 존재한다면, 해당 부모 테이블의 행을 삭제하거나 업데이트할 수 없습니다. 이는 자식 데이터가 유지되는 것을 의미하게 됩니다.
CASECADE
부모 테이블의 행이 삭제되거나 업데이트될 때, 그와 연결된 자식 테이블의 행들도 같이 삭제되거나 업데이트 됩니다.
즉, 부모 테이블의 행이 변경되면(삭제 또는 업데이트), 그에 따라 자식 테이블의 연관된 행들도 자동으로 변경되는 것을 의미합니다.
SET NULL
부모 테이블의 행이 삭제되거나 업데이트될 때, 참조하는 자식 테이블의 외래 키 컬럼 값이
NULL로 설정됩니다.
부모 테이블의 특정 행과 관계를 끊고 싶지만, 자식 테이블의 행 자체를 삭제하고 싶지 않을 때 사용합니다. 부모 테이블의 행이 삭제되면 자식 테이블의 외래 키 값이
NULL로 설정되고, 연결이 끊어집니다.여기서 끊어진다는 상황의 의미는 부모 테이블의 행에 대한 직접적인 참조가 제거되고, 자식 테이블의 해당 외래 키 컬럼 값이
NULL로 설정됨으로써, 그 행이 더이상 특정한 부모 테이블의 행과 관련이 없다는 것을 의미합니다. 이로 인해 데이터의 무결성은 유지되지만, 부모-자식 간의 관계는 해제됩니다.즉,
SET NULL은 부모 테이블과의 관계를 유지하지 않으면서도 자식 테이블의 행을 유지하고자 할 때 사용됩니다.외래키가
NULL을 허용하는 경우에만 가능하며, 외래 키 컬럼이 NOT NULL 제약 조건을 가지고 있지 않을 때 적용될 수 있습니다.
예를 아래에 설명하겠습니다. 어떤 상황에서 사용하는지 헷갈릴 경우 참고하면 좋을 것 같아요 !
학생 테이블이 학교 테이블의 학교_ID를 외래 키로 참조하고 있고,
이 외래 키 제약 조건에 SET NULL 옵션이 설정되어 있다고 가정해 볼게요.
이 경우 각 학생 레코드는 학교_ID 필드를 가지고 있으며, 이 필드는 학교 테이블의 특정 학교를 참조합니다.
만약 학교 테이블에서 어떤 학교_ID를 가진 학교가 폐교되어
해당 학교 레코드가 삭제되면,
SET NULL 옵션에 따라 학생 테이블의 해당 학교_ID를 가진 모든 레코드의 학교_ID 필드는 NULL로 설정됩니다.
즉, 그 학생들은 더 이상 유효하지 않은(삭제된) 학교를 참조하지 않게 되고, 학교_ID 필드가 NULL로 설정되어 해당 학생들이 어떤 학교에도 소속되어 있지 않음을 나타내게 됩니다.
이는 데이터베이스 상에서 학교가 폐교되더라도 학생 레코드 자체는 삭제되지 않고 계속 유지되며, 학생들은 더 이상 어떤 학교에도 소속되지 않은 상태가 되는 것을 의미합니다.
