[MySQL] SET FOREIGN_KEY_CHECKS 완벽 가이드 사용법(외래 키 제약 조건 활성/비활성)

MySQL SET FOREIGN_KEY_CHECKS는 외래 키 제약 조건(foreign key constraints)의 검사 여부를 설정하는 데 사용되는 중요한 SQL 명령어입니다. 데이터베이스에서 외래 키는 데이터의 무결성을 유지하는 데 중요한 역할을 하지만, 때로는 데이터 복구나 대량 삽입 작업 중 외래 키 검사를 임시로 비활성화해야 할 때가 있습니다. 이 명령어는 이러한 상황에서 매우 유용합니다.

SET FOREIGN_KEY_CHECKS 기본 문법

SET FOREIGN_KEY_CHECKS = {0 | 1};

• 0: 외래 키 검사 비활성화
• 1: 외래 키 검사 활성화 (기본값)

이 명령어는 세션 범위에서 작동하며, 한 번 설정하면 해당 세션이 종료될 때까지 유지됩니다.

반응형

SET FOREIGN_KEY_CHECKS의 주요 사용 사례

1. 대량 데이터 삽입 또는 갱신
   외래 키 제약 조건을 비활성화하면 대량 데이터 작업의 성능을 크게 향상시킬 수 있습니다.
2. 테이블 구조 변경
   외래 키가 설정된 테이블을 수정하거나 삭제할 때 제약 조건을 임시로 비활성화해야 할 수 있습니다.
3. 백업 및 복원 작업
   데이터베이스 백업을 복원할 때 외래 키 검사를 비활성화하여 복원 작업을 원활히 진행할 수 있습니다.

외래 키 검사 비활성화 예제

다음은 외래 키 검사를 비활성화하고 데이터 삽입 작업을 수행한 후 다시 활성화하는 예제입니다:

SET FOREIGN_KEY_CHECKS = 0;

INSERT INTO orders (order_id, customer_id) VALUES (1, 1001);  
INSERT INTO orders (order_id, customer_id) VALUES (2, 1002);

SET FOREIGN_KEY_CHECKS = 1;

위 코드에서 외래 키 검사가 비활성화된 동안 orders 테이블에 데이터를 삽입합니다. 이후 외래 키 검사를 다시 활성화합니다.

테이블 삭제 시 외래 키 검사 비활성화

외래 키로 연결된 테이블을 삭제하려면 다음과 같이 외래 키 검사를 비활성화해야 합니다:

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE orders;
DROP TABLE customers;

SET FOREIGN_KEY_CHECKS = 1;

orders 테이블이 customers 테이블과 외래 키로 연결되어 있는 경우, 외래 키 검사를 비활성화하지 않으면 오류가 발생할 수 있습니다.

반응형

SET FOREIGN_KEY_CHECKS를 사용할 때의 주의사항

1. 데이터 무결성 손상 가능성
   외래 키 검사를 비활성화하면 데이터 무결성이 손상될 위험이 있습니다. 삽입 또는 삭제 작업 후 데이터가 일관성을 유지하도록 주의해야 합니다.
2. 다중 세션 환경
   SET FOREIGN_KEY_CHECKS는 현재 세션에만 영향을 미치므로, 다중 세션 환경에서는 다른 세션이 영향을 받지 않습니다.
3. 비활성화 후 활성화 필요
   작업이 완료되면 반드시 외래 키 검사를 다시 활성화하여 데이터 무결성을 보장해야 합니다.

응용 예제: 데이터 복원 스크립트

다음은 데이터베이스 백업을 복원할 때 사용하는 예제입니다:

SET FOREIGN_KEY_CHECKS = 0;

INSERT INTO customers (customer_id, name) VALUES (1001, 'Alice');
INSERT INTO customers (customer_id, name) VALUES (1002, 'Bob');

INSERT INTO orders (order_id, customer_id) VALUES (1, 1001);
INSERT INTO orders (order_id, customer_id) VALUES (2, 1002);

SET FOREIGN_KEY_CHECKS = 1;

이 스크립트는 외래 키 검사를 비활성화하여 customers와 orders 테이블에 데이터를 삽입한 뒤, 검사를 다시 활성화합니다.

실제 데이터베이스에서의 사용 예제

다음은 복잡한 테이블 관계에서 외래 키 검사를 비활성화한 뒤 데이터를 대량 삭제하는 방법입니다:

SET FOREIGN_KEY_CHECKS = 0;

DELETE FROM orders WHERE order_date < '2023-01-01';
DELETE FROM customers WHERE last_purchase_date < '2022-01-01';

SET FOREIGN_KEY_CHECKS = 1;

이 코드는 과거 데이터를 대량 삭제할 때 외래 키 제약 조건이 발생하지 않도록 설정합니다.

반응형

결론

SET FOREIGN_KEY_CHECKS는 MySQL에서 대량 데이터 작업, 테이블 구조 변경, 또는 데이터베이스 복원과 같은 작업을 수행할 때 매우 유용한 명령어입니다. 다만, 외래 키 검사를 비활성화한 동안 데이터 무결성을 주의 깊게 관리해야 합니다. 이 명령어를 올바르게 사용하면 작업의 효율성을 극대화할 수 있습니다.

자세한 내용은 MySQL 공식 문서에서 확인할 수 있습니다:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_foreign_key_checks