[TIL][error]데이터 삭제 _delete,truncate 차이

김예진·2020년 12월 6일

TIL error

TIL

목록 보기

31/40

❗️에러 상황

저장된 데이터를 지워야할 때마다 이전에는 delete 명령어를 사용한 후에 ALTER TABLE 테이블명AUTO_INCREMENT = 0; 명령어로 테이블 생성시의 초기 값 부터 생성되도록 했다.

그러다가 ....TRUNCATE를 알게돼서 TRUNCATE를 쓰려고 했는데

error 메세지 :

truncate table projects;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`insahr_intranet`.`project_details`, CONSTRAINT `project_details_project_detail_id_d40381c0_fk_projects_id`)

부모인 projects 테이블과 project FK를 가진 project_details 테이블을 지우려고 했는데
"외래 키 제약 조건에서 참조 된 테이블을자를 수 없습니다."라는 에러 메세지가 떴다.
흠, 그럼 project_details 테이블에 있는 데이터를 먼저 TRUNCATE하고 projects 테이블을 TRUNCATE해주면 되겠지라고 생각했지만

project_details는 TRUNCATE가 됐지만 projects는 끝까지 계속 똑같은 오류를 내면서 지워지지 않았다. 도대체 왜!

다시 구글링!

위와 같은 오류를 해결하려면 truncate table 명령어 전에 foreign_key_checks 설정값을 변경하여 테이블의 데이터를 제거할 수 있다.
아래는 사용 예제 이다.
set foreign_key_checks = 0;
truncate table tb_contact_answer_log;
set foreign_key_checks = 1;
foreign_key_checks 는 InnoDB 테이블의 외래 키 제약 조건을 검사할지 여부를 지정한다.
또한 위 방법을 이용하면 무결성 검사 없이 입력을 할 수 있다.
아래는 무결성 검사를 무시하고 데이터를 입력하는 예제이다.
SET FOREIGN_KEY_CHECKS = 0;
insert into tb_contact_answer_log (contact_idx, manager_idx, touch_dt) values (1, 2, now());
SET FOREIGN_KEY_CHECKS = 1;
출처: https://aith.tistory.com/130 [Newbie Web Development]

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table $table_name;
SET FOREIGN_KEY_CHECKS = 1;

하려고 했지만 그것 마저 오류 메세지 ... 계속 매달려있을 수 없어서 delete 명령어로 데이터를 삭제하고 초기값을 설정해서 다시 작업을 진행했다.

진행하는 내내 왜 delete로는 잘 지워지는데 truncate로는 지워지지 않을까 궁금해서 ... 알아봐야겠다 싶었다.

drop,truncate와 delete간에 차이

DELETE
: 데이터는 지워지지만 테이블 용량은 줄어 들지 않는다. 원하는 데이터만 지울 수 있다. 삭제 후 잘못 삭제한 것을 되돌릴 수 있다.
TRUNCATE
: 용량이 줄어 들고, 인덱스 등도 모두 삭제 된다. 테이블은 삭제하지는 않고, 데이터만 삭제한다. 한꺼번에 다 지워야 한다. 삭제 후 절대 되돌릴 수 없다.
DROP
: 테이블 전체를 삭제, 공간, 객체를 삭제한다. 삭제 후 절대 되돌릴 수 없다.

truncate와 delete의 차이를 조금 더 찾아봤다.

DDL과 DML

TRUNCATE는 DDL, DELETE 는 DML 입니다.

DDL(Data Definition Language)은 데이터를 정의하는 언어로서 개체를 만들고 변경, 삭제하는 CREATE, ALTER, DROP문과 같은 것들을 말한다.

DML(Data Manipulation Language)은 데이터 조작 언어로서 데이터를 가공하는 SELECT, INSERT, UPDATE, DELETE문과 같은 것들을 말한다.

출처: https://pointnet.tistory.com/334 [점넷(.Net) 아지트]