❗️에러 상황
저장된 데이터를 지워야할 때마다 이전에는 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) 아지트]
데이터를 삭제하는 방식의 차이
DELETE와 TRUNCATE의 가장 큰 차이점은 데이터를 삭제하는 방식이다.
DELETE의 경우 데이처의 FK제약을 체크하고, 데이터를 한줄씩 순차적으로 삭제한다.
TRUNCATE은 테이블간의 제약을 체크하고, 테이블 DROP 후 CREATE를 수행한다.
결국, 사용 목적에 따라 적절한 명령문을 사용하는 게 최선이....다....라는 결론...
이해할 때 참고된 게시글 :https://database.sarang.net/?inc=read&aid=9917&criteria=pgsql&subcrit=&id=&limit=20&keyword=&page=4
