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

회원탈퇴를 위해, user 테이블에서 userId=2 인 레코드를 삭제하려고 할때, 발생한 에러이다. 에러코드에 따르면, posts 테이블에서 user 테이블의 id를 foreign key로 참조하고 있기 때문에 user 테이블의 레코드를 삭제할 수 없다고 한다.

이는 foreign key로 관계가 설정된 다른 테이블에 데이터 또는 데이블 자체가 존재하지 않는 값/테이블을 참조하는 상태가 되는 것을 DBMS에서 방지하기 때문에 발생한다.

이를 해결하는 방법은 다음과 같다.

잠시 foreign key 설정 풀기

SET FOREIGN_KEY_CHECKS=0;
DELETE FROM users WHERE id=2; //DROP TABLE users; 레코드 또는 테이블을 삭제/업데이트 할 수 있다
SET FOREIGN_KEY_CHECKS=1;

참조 테이블의 데이터를 먼저 지우기

DELETE FROM comments WHERE writerId=2;
DELETE FROM posts WHERE writerId=2;
DELETE FROM users WHERE id=2;

on delete cascade

foreign key에 on delete cascade 제약사항 추가하기

//테이블 수정 문법
ALTER TABLE 테이블이름
ADD CONSTRAINT 제약조건이름
FOREIGN KEY (필드이름)
REFERENCES 테이블이름 (필드이름)

//테이블 수정 적용
ALTER TABLE posts 
ADD CONSTRAINT writerId
FOREIGN KEY (writerId) 
REFERENCES users(id);

//테이블 생성 문법
CREATE TABLE 테이블이름
 (
  필드이름 필드타입,
  ...,
 FOREIGN KEY (필드이름)
    REFERENCES 테이블이름 (필드이름)
	ON DELETE CASCADE
);

//테이블 생성 적용
CREATE TABLE rooms (
    room_no INT PRIMARY KEY AUTO_INCREMENT,
    room_name VARCHAR(255) NOT NULL,
    building_no INT NOT NULL,
    FOREIGN KEY (building_no)
        REFERENCES buildings (building_no)
        ON DELETE CASCADE
);

reference

• https://www.mysqltutorial.org/mysql-on-delete-cascade/
• https://www.geeksforgeeks.org/mysql-on-delete-cascade-constraint/
• http://tcpschool.com/mysql/mysql_constraint_foreignKey