[ MySQL ] 외래키 설정으로 인한, 데이터 삭제 변경이 안되는 경우 해결 방법 3가지

MySQL의 InnoDB엔진을 사용하는 경우, 외래키를 사용할 수 있습니다. 

외래키는 부모 테이블과 자식 테이블 간의 관계를 위해 생성하는 키로, 해당 컬럼에 인덱스 생성이 필요하며 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 존재하는지 체크하는 작업이 필요합니다. 

 

개발 환경에서는 위와같은 테이블간의 관계가 좋은 가이드 역할을 할 수도 있지만, 테이블간의 체크로 인해 잠금이 여러 테이블로 전파하여 데드락이 발생하는 경우도 있습니다. 

또한, 부모와 자식 테이블의 관계를 생각안하고 데이터를 삭제 및 변경 하는 경우에도 작업이 실패하는 경우게 생기게됩니다. 

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

위의 에러는, 외래키 관계를 생각안하고 데이터를 삭제 및 변경을 시도하는 경우에 발생하는 에러입니다. 

이렇게 외래키 설정으로 인해서 데이터를 변경 및 삭제가 실패하는 경우를 해결하는 방법에 대해서 설명해보려고 합니다. 

 

 

실습을 위한 테이블 생성 및 데이터 추가

---

CREATE TABLE table1 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    column1 VARCHAR(10),
    column2 VARCHAR(10)
);

CREATE TABLE table2 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    table_id INT,
    column1 VARCHAR(10),
    FOREIGN KEY (table_id) REFERENCES table1(id)
);

table1과 table2를 추가하여, table2의 table_id 컬럼에 외래키를 설정하여 table1 과의 관계를 설정했습니다. 

부모 테이블 : table1

자식 테이블 : table2 

INSERT INTO table1(column1, column2) VALUES ('A', 'B'), ('C', 'D'), ('E', 'F');
INSERT INTO table2(table_id, column1) VALUES (1, 'A'), (1, 'B'), (3, 'C');

 

 

 

자식 테이블의 데이터를 먼저 삭제

---

부모와 자식 테이블의 관계를 생각하여, 순서대로 자식의 테이블의 데이터를 먼저 삭제하고 부모 테이블의 데이터를 삭제를 하게 되면, 에러가 발생하지 않고 데이터를 삭제하는 것이 가능합니다. 

DELETE FROM table1 WHERE id = 1;

자식 테이블에서 부모 테이블의 id컬럼값이 1인 레코드를 참조하고 있는 데이터를 먼저 삭제하기 전에, 부모 테이블의 레코드를 삭제하게 되면 ,

위와같은 에러가 발생하게 됩니다. 

DELETE FROM table2 WHERE table_id = 1;
DELETE FROM table1 WHERE id = 1;

연관이 있는 자식 테이블의 레코드를 먼제 삭제하고 난 뒤, 부모 테이블 레코드를 삭제하면 에러없이 실행이 가능합니다.

 

 

CASCADE  / SET NULL 사용

---

부모 테이블의 레코드가 삭제시, 자식 테이블에서 연관이 되어있는 레코드를 같이 삭제하거나 해당 컬럼을 NULL로 변경해주는 설정을 추가하는 것이 가능합니다. 

FOREIGN KEY (table_id) REFERENCES table1(id) ON [DELETE / UPDATE] [CASCADE / SET NULL]

위와같이 외래키 설정시에 같이 옵션으로 추가할 수 있습니다.

ON [DELETE/UPDATE]  : 삭제시 / 변경시

CASCADE : 동일한 작업 실시 (부모 테이블 레코드를 삭제하면 연관있는 자식 테이블 레코드도 삭제) 

SET NULL : 부모 테이블의 레코드를 변경시 연관있는 자식 테이블의 레코드 해당 컬럼을 NULL로 변경

 

CREATE TABLE table2 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    table_id INT,
    column1 VARCHAR(10),
    FOREIGN KEY (table_id) REFERENCES table1(id) ON DELETE CASCADE
);

위와같이 테이블을 다시 생성하고, 외래키의 옵션에 ON DELETE CASCADE 설정을 추가합니다. 

 

DELETE FROM table1 WHERE id = 1;

그 상태에서 부모 테이블의 레코드를 삭제하게 되면, 에러가 발생하지 않고 실행할 수 있게 됩니다.

 

SELECT * FROM table2;

자식 테이블을 확인해 보면,

table_id컬럼이 1의 값을 가지는 레코드도 부모 테이블과 함께 같이 삭제된 것을 확인 할 수 있습니다. 

 

 

시스템변수 foreign_key_checks 사용

---

시스템변수 foreign_key_checks의 값을 OFF로 변경하게 되면, 외래키로 인한 테이블의 관계 체크 작업을 무시할 수 있습니다. 

SET foreign_key_checks=OFF;
SET SESSION foreign_key_checks=OFF;

위의 두 명령은 동일한 작업을 합니다. (SESSION을 생략하면 자동으로 SESSION으로 실행됨)

 

CREATE TABLE table2 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    table_id INT,
    column1 VARCHAR(10),
    FOREIGN KEY (table_id) REFERENCES table1(id)
);

자식 테이블은 CASCADE등의 옵션을 추가하지 않은 외래키만 추가하여 생성했습니다. 

 

그 상태에서, 부모 테이블 레코드를 삭제하면,

DELETE FROM table1 WHERE id = 1;

foreign_key_checks값을 OFF로 변경 하기 전에는, 자식 테이블에서 부모 테이블의 레코드를 참조하고 있었기 때문에 에러가 발생했지만 

OFF로 변경한 뒤로는 에러없이 삭제되는 것을 확인할 수 있습니다.

 

⚠️주의!
foreign_key_checks의 값을 OFF로 변경하고, 위와같이 관계 상관없이 데이터를 변경할 수 있다고해서 두 테이블의 관계가 깨진 상태로 데이터를 유지해도 된다는 것은 아닙니다. 관계가 유지될 수 있도록 관련있는 자식 테이블 레코드도 삭제가 필요합니다. 

또한, foreign_key_checks값이 OFF상태인 경우에는, CASCADE의 옵션도 무시하므로 주의가 필요합니다.

 

---

이번에는 외래키 사용에 있어서 두 테이블의 관계를 맺어 개발시에 좋은 가이드 역할을 하기도 하지만, 데이터 작업시 연관 테이블간에 체크 과정으로 인한 작업 실패를 해결할 수 있는 방법에 대해서 알아봤습니다.