[조각SQL] ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails 해결하기, 이미 생성된 테이블에 cascade 적용

MariaDB [db]> delete from table where id > 170000;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db`.`table`, CONSTRAINT `table_ibfk_1` FOREIGN KEY (`prev_id`) REFERENCES `table` (`id`))

테이블에서 데이터를 삭제하려고 시도하니 아래와 같은 로그가 떴다.

이는 다른 테이블에 해당 데이터를 참조하고 있는 외래키가 있기 때문이다.

보통 테이블을 생성할 때 cascade 옵션을 사용하면, pk가 지워지면 해당 pk를 fk로 참조하는 데이터로 연계되어 삭제가 되지만, 내 경우에는 처음에 cascade 옵션을 모르는 상태여서 적용되지 못하였다 ㅠㅠ 

 

그래서 다음과 같은 형태로 cascade를 적용하였다. 

1. 해당 칼럼을 신규로 복사
2. 이후 해당 칼럼에 constraint를 부여하며 on delete cascade 옵션 부여

그럼 SQL 문으로 보자.

# 복사한 데이터를 담을 신규칼럼 생성
ALTER TABLE table_name ADD copy_column int;

# 원래 칼럼에 있던 데이터를 복사
UPDATE table_name SET copy_column = original_column;

# fk 제약 삭제
ALTER TABLE table_name DROP FOREIGN KEY table_ibfk_1;

# fk constraint를 담고 있는 원래 칼럼 삭제
ALTER TABLE table_name DROP COLUMN original_column;

# 복사한 col에 constraint 부여하기
ALTER TABLE table_name 
  ADD CONSTRAINT table_ibfk_1 # constraint name
  FOREIGN KEY copy_column
  REFERENCES parent_table_name(parent_column_name) 
  ON DELETE CASCADE;

 

왜 alter문으로 바로 안넣고 굳이 신규 칼럼을 생성해서 cascade를 부여하는지?

아래 링크를 참조하면 좋을 것 같다. column에 constraint를 부여하고 난 이후는 수정이 불가능하기 때문이다. constraint가 부여되지 않은 column에는 부여가 가능하지만, 이미 부여된 후에는 수정이 불가능한 것!

How to add 'ON DELETE CASCADE' in ALTER TABLE statement

보통 외래키 참조를 해제하는 방법도 쓰시는 것 같은데(set을 통해 사용하는 것), 다시 재설정하게 되는 경우 데이터가 깨질 위험이 있기 때문에 나는 따로 적용하지 않았다.

 

잘도니거 맞겠지...? (쭈굴...) 일단 난 잘 먹혔다 ㅎㅎ