[MySQL] 01. 제약조건 무시하고 테이블 데이터 삭제 방법 - DELETE, TRUNCATE 차이 간단 정리

01. 데이터 삭제

아래 작업은 상용이 아닌 LOCAL, DEV 작업에서만 하는게 좋을 것 같다고 생각한다. DEV도 회사에 따라서 중요한 데이터로 사용이되고 있을 수 있기에 조심해서 사용하자.

DELETE TABLE [테이블명] or TRUNCATE [테이블명] ERROR 1217 (23000): 
Cannot delete or update a parent row: a foreign key constraint fails

• 로컬에서 테스트 중 시퀸스(seq)를 1부터 다시 초기화하고, 새로운 데이터를 넣고 있었던 과정
• 로컬이기에 데이터를 밀어버리고 다시 넣으려고 하였으나, A 테이블과 B 테이블이 서로 참조 관계에 있었기에 삭제 불가능
• 이는 MySQL이 테이블 간 관계가 있는 데이터를 함부로 삭제할 수 없게 안전장치를 해두었기 때문이다

02. 해소 방안

# 외래키 검사 활성화 or 비활성화
# 0: 비활성화, 1: 활성화 -> 해당 옵션은 웬만하면 건들지 말자
SET foreign_key_checks = 0;

DELETE TABLE [테이블명] or TRUNCATE [테이블명]

SET foreign_key_checks = 1; // 다시 체크 설정

• 그럼에도 불구하고 데이터를 삭제한다고 가정하면, 위 명령어를 통해 해소 가능하다
  • SET foreign_key_checks = 0 명령어를 통해, 외래키 검사 조건을 비활성화로 만든다
  • 후에 데이터가 모두 삭제가 되었으면, 다시 원복 해주면 된다
• 위 명령어는 DELETE, TRUNCATE에 국한되지 않고 DROP에서도 사용이 가능하다 한다
• 여튼 DELETE, TRUNCATE, DROP은 조심해야 할 부분이 있으니, 사용할거면 어려번 체크하고 사용하자

02. DELETE, TRUNCATE

02-1. DELETE

• 조건부 삭제 : DELETE절은 WHERE절을 사용하여 조건에 맞는 행만 삭제 가능
• 시퀸스 유지 : DELETE절은 TRUNCATE와는 다르게 테이블 데이터 삭제해도 시퀸스는 초기화되지 않음
• 롤백 가능 : DELETE절은 트랜잭션 내에서 실행 가능하며, 필요한 경우 롤백 가능
• 성능 고려 : DELETE절은 TRUNCATE보다 더 많은 시스템 자원 사용, 각 행을 개별적으로 삭제 + 관련 인덱스 갱신하기 때문

02-2. TRUNCATE

• 데이터 초기화 : TRUNCATE절은 모든 데이터를 빠르게 제거한다. 테이블 구조는 유지 + 데이터만 삭제
• 시퀸스 초기화 : TRUNCATE절 시퀸스도 초기화한다. 즉, 1부터 다시 시작하도록 수정해줌
• 롤백 불가 : TRUNCATE절은 대부분의 DB에서 롤백이 불가능함(PostgreSQL)과 같읕 일부 DB는 가능
• 빠른 실행 : 대량 데이터를 가진 테이블에서 DELETE보다 속도가 훨씬 빠름

99. 참고 자료

Mysql - foreign key 무시하고 데이터 삭제하기

[MariaDB/Mysql] 제약조건 무시하고 테이블 삭제하기