MySQL 테이블 제약 사항 확인 방법

테이블의 데이터를 delete 할 때 만약 해당 테이블이 부모 테이블이면 외래키 제약(Constraint)에 의해 삭제가 불가능 할 수 있다.

그래서 테이블을 update 또는 delete 하기 전엔 다른 테이블과 관계 맺고 있는지 여부를 확인하는 게 좋다.

mysql에서는 INFORMATION_SCHEMA 데이터베이스의 KEY_COLUMN_USAGE 테이블에 그 정보가 있다.

1. INFORMATION_SCHEMA.KEY_COLUMN_USAGE 조회

SELECT 
 *
FROM
 INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
 REFERENCED_TABLE_NAME = 'ParentTable';

위 쿼리에서 ‘ParentTable’ 에 확인하려는 부모 테이블명을 넣으면 된다. 내 경우엔 실습을 위해 ParentTable 이라는 부모 테이블과 ChildTable1, ChildTable2 라는 자식 테이블 두 개를 만들어 놨다.

• TABLE_NAME : 자식 테이블명
• COLUMN_NAME : 자식 테이블 외래키 컬럼
• REFERENCED_TABLE_NAME : 부모 테이블명
• REFERENCED_COLUMN_NAME : 참조되는 부모 테이블 컬럼

조회 결과를 보면 ParentTable의 ID 컬럼을 참조하고 있는 ChildTable1, ChildTable2 가 있기 때문에 자식 테이블이 참조하고 있는 ID 컬럼이 부모 테이블에 존재하면 그 데이터를 delete 하거나 update 할 수 없다는 걸 알 수 있다.

2. 제약 확인 후 DELETE

부모 테이블에 있는 데이터를 delete 하기 위해서는 자식 테이블의 외래키를 먼저 삭제해야 된다.

예를 들어 사용하려는 쿼리가

DELETE FROM ParentTable WHERE ID ='1'

이런 거면

DELETE FROM ChildTable1 where ParentID IN (SELECT ID FROM ParentTable WHERE ID ='1')
DELETE FROM ChildTable2 where ParentID IN (SELECT ID FROM ParentTable WHERE ID ='1')

이렇게 해당 데이터를 포함하는 자식 데이터를 삭제해 준다.

물론 이 경우엔

DELETE FROM ChildTable1 where ParentID='1'
DELETE FROM ChildTable2 where ParentID='1'

처럼 간단하게 쓸 수 있긴 한데

첫번째 방법은 부모 테이블에 있는 다른 컬럼을 기준으로 삭제할 때도 응용해서 쓸 수 있다는 장점이 있다. 예를 들면 이런 거다.

DELETE FROM ChildTable1 where ParentID IN (SELECT ID FROM ParentTable WHERE Data ='Parent Data 1')

하지만 쿼리 성능은 서브 쿼리를 안 쓰는 게 더 나을테니 where ParentID=’1′ 처럼 자식 테이블 안에서 해결할 수 있으면 더 좋다.

참고로 자식 테이블 제약에 대해 CASCADE 옵션을 써서 부모 테이블 delete 시 자동으로 함께 지워지도록 하는 방법도 있긴 한데 이건 테이블 제약을 임의로 깨는 거라 앞으로 이 테이블을 안 쓰려고 하는 게 아니면 쓰기가 좀 부담스럽다.