MariaDB FOREIGN KEY ERRORS

`FOREIGN KEY ERRORS`는 외래 키 제약 조건(Foreign Key Constraint)이 위반되었을 때 발생하는 오류. 외래 키는 두 테이블 간의 관계를 정의하며, 자식 테이블의 특정 컬럼 값이 부모 테이블의 값과 일치해야 한다. 아래 예시와 함께 외래 키 오류의 원인 및 해결 방법을 설명한다.

 

부모 테이블에 존재하지 않는 값을 자식 테이블에 삽입하려고 할 때 발생. 또는 부모 테이블의 레코드를 삭제하거나 수정할 때 자식 테이블에 참조되는 레코드가 있는 경우에도 오류가 발생.

 

예를 들어, 'orders' 테이블이 'customers' 테이블의 'customer_id'를 참조하는 경우. 이때 'customers'에 없는 'customer_id'를 'orders'에 삽입하려고 하면 외래 키 오류가 발생. 또 'customers' 테이블에서 특정 고객을 삭제하려는데 'orders' 테이블에 그 고객의 주문이 남아있으면 삭제 불가.

또 다른 예시는 ON DELETE나 ON UPDATE 옵션이 설정되어 있지 않아서 발생하는 문제. CASCADE 옵션이 설정되지 않았다면 부모 테이블의 변경이 자식 테이블에 전파되지 않아 오류발생.

'SHOW ENGINE INNODB STATUS'의 출력에서 LATEST FOREIGN KEY ERROR 섹션을 분석하는 방법은. 어떤 부모 테이블과 자식 테이블이 관련되어 있는지, 어떤 제약 조건이 위반되었는지, 관련된 SQL 문은 무엇인지 등을 확인.

해결방법은 누락된 부모 키 값을 추가하거나, CASCADE 옵션을 설정, 잘못된 데이터를 정정.

---

 1. 예시 시나리오
 테이블 구조

-- 부모 테이블 (Parent Table)
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 자식 테이블 (Child Table)
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) 
        REFERENCES customers(customer_id) 
        ON DELETE CASCADE
);
```

---

 2. 외래 키 오류 발생 예시

 Case 1: 부모 테이블에 없는 값 삽입

-- customers 테이블에 customer_id=1이 없음
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (100, 1, '2023-10-10');
```
- 오류 메시지:
  ```plaintext
  ERROR 1452 (23000): Cannot add or update a child row: 
  a foreign key constraint fails (`test`.`orders`, 
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) 
  REFERENCES `customers` (`customer_id`))
  ```
- 원인: `orders` 테이블의 `customer_id=1`이 `customers` 테이블에 존재하지 않음.

 Case 2: 부모 테이블의 레코드 삭제 (CASCADE 미설정 시)

-- ON DELETE CASCADE 없이 orders 테이블 생성 시
DELETE FROM customers WHERE customer_id = 1;
```
- 오류 메시지:
  ```plaintext
  ERROR 1451 (23000): Cannot delete or update a parent row: 
  a foreign key constraint fails (`test`.`orders`, 
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) 
  REFERENCES `customers` (`customer_id`))
  ```
- 원인: `orders` 테이블에서 `customer_id=1`을 참조하는 레코드가 있는데, `ON DELETE CASCADE`가 설정되지 않음.

---

 3. `SHOW ENGINE INNODB STATUS`에서 외래 키 오류 분석
`LATEST FOREIGN KEY ERROR` 섹션에서 상세 정보를 확인할 수 있습니다.

 예시 출력
```plaintext
LATEST FOREIGN KEY ERROR
------------------------
2023-10-10 14:20:00 0x7f8b12345678
Transaction:
TRANSACTION 123456789, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 10, OS thread handle 123456789, query id 987654321 localhost root update
INSERT INTO orders (order_id, customer_id, order_date) VALUES (100, 1, '2023-10-10')

Constraint violation:
FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`)
```

- 주요 항목:
  - 오류 발생 시간: `2023-10-10 14:20:00`.
  - 위반된 제약 조건: `FOREIGN KEY (customer_id) REFERENCES customers(customer_id)`.
  - 실행된 쿼리: `INSERT INTO orders ...`.
  - 원인: `customers` 테이블에 `customer_id=1`이 존재하지 않음.

---

 4. 해결 방법

 1) 부모 테이블에 누락된 데이터 추가

-- customers 테이블에 customer_id=1 추가
INSERT INTO customers (customer_id, name) 
VALUES (1, 'John Doe');
```

 2) ON DELETE/UPDATE 옵션 설정
- CASCADE: 부모 테이블의 변경 사항을 자식 테이블에 자동 반영.
  
  ALTER TABLE orders 
  ADD FOREIGN KEY (customer_id) 
      REFERENCES customers(customer_id) 
      ON DELETE CASCADE 
      ON UPDATE CASCADE;
  ```

 3) 잘못된 데이터 수정

-- orders 테이블의 customer_id를 유효한 값으로 변경
UPDATE orders 
SET customer_id = 2 
WHERE customer_id = 1;
```

---

 5. 예방 방안
1. 데이터 무결성 검증:  
   애플리케이션 레벨에서 외래 키 값의 유효성을 사전에 확인한다.
2. CASCADE 옵션 활용:  
   부모-자식 테이블 간의 동기화를 자동화한다.
3. 트랜잭션 관리:  
   외래 키 관련 작업은 트랜잭션으로 묶어 원자성을 보장한다.
4. 오류 로그 모니터링:  
   `SHOW ENGINE INNODB STATUS`를 주기적으로 확인해 외래 키 오류를 사전에 차단한다.

---