MySQL에서 특정 테이블이나 열에 연결된 모든 외래 키(Foreign Key)를 확인하려면 INFORMATION_SCHEMA.KEY_COLUMN_USAGE 뷰를 조회하는 것이 가장 확실한 방법입니다. 이 뷰에는 데이터베이스 내 모든 키 제약 조건 정보가 담겨 있으며, WHERE 절에서 참조 테이블 이름(REFERENCED_TABLE_NAME)을 조건으로 지정하면 해당 테이블을 참조하는 외래 키 목록을 한눈에 파악할 수 있습니다.
예제 테이블 준비하기
먼저 두 개의 테이블을 생성한 뒤, 외래 키 제약 조건으로 서로 연결해 보겠습니다.
첫 번째 테이블 생성
mysql> CREATE table ForeignTable
-> (
-> id int,
-> name varchar(200),
-> Fk_pk int
-> );
Query OK, 0 rows affected (0.43 sec)
두 번째 테이블 생성
첫 번째 테이블 생성이 완료되면, 기본 키(Primary Key)를 포함하는 두 번째 테이블을 아래와 같이 만듭니다.
mysql> CREATE table primaryTable1
-> (
-> Fk_pk int,
-> DeptName varchar(200),
-> Primary key(Fk_pk)
-> );
Query OK, 0 rows affected (0.48 sec)
외래 키 제약 조건 추가하기
이제 ALTER TABLE 명령과 FOREIGN KEY 제약 조건을 사용해 두 테이블을 연결합니다. 기본 문법은 다음과 같습니다.
ALTER TABLE 첫번째테이블명
ADD CONSTRAINT 제약조건이름
FOREIGN KEY (외래 키로 사용할 열 이름)
REFERENCES 두번째테이블명 (기본 키 역할을 하는 열 이름);
위 문법을 실제로 적용해 두 테이블을 연결해 보겠습니다.
mysql> alter table ForeignTable add constraint constFKPK foreign key(Fk_pk) references primaryTable1(Fk_pk);
Query OK, 0 rows affected (1.57 sec)
Records: 0 Duplicates: 0 Warnings: 0
테이블의 모든 외래 키 조회하기
특정 테이블을 참조하는 모든 외래 키를 확인하려면 다음과 같은 쿼리를 사용합니다.
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME,
REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = '참조되는_테이블_이름';
위 문법을 적용해 primarytable1 테이블을 참조하는 외래 키를 조회하는 쿼리는 다음과 같습니다.
mysql> SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME,
-> REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
-> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
-> WHERE REFERENCED_TABLE_NAME = 'primarytable1';
실행 결과는 다음과 같습니다.
+--------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+--------------+-------------+-----------------+-----------------------+------------------------+
| foreigntable | Fk_pk | constFKPK | primarytable1 | fk_pk |
+--------------+-------------+-----------------+-----------------------+------------------------+
1 row in set, 2 warnings (0.02 sec)
결과를 보면 제약 조건 이름(CONSTRAINT_NAME)은 'constFKPK'이고, 외래 키가 정의된 테이블(TABLE_NAME)은 'foreigntable'임을 알 수 있습니다.
특정 열의 모든 외래 키 조회하기
테이블 전체가 아니라 특정 열을 참조하는 외래 키만 확인하고 싶다면, 데이터베이스 이름과 참조 열 이름까지 함께 조건으로 지정하면 됩니다.
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME,
REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = '데이터베이스_이름'
AND REFERENCED_TABLE_NAME = '참조되는_테이블_이름'
AND REFERENCED_COLUMN_NAME = '참조되는_열_이름';
위 문법을 적용해 business 데이터베이스의 primarytable1 테이블 fk_pk 열을 참조하는 외래 키를 조회하는 쿼리는 다음과 같습니다.
mysql> SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME,
-> REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
-> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
-> WHERE REFERENCED_TABLE_SCHEMA = 'business'
-> AND REFERENCED_TABLE_NAME = 'primarytable1'
-> AND REFERENCED_COLUMN_NAME = 'fk_pk';
실행 결과는 다음과 같습니다.
+--------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+--------------+-------------+-----------------+-----------------------+------------------------+
| foreigntable | Fk_pk | constFKPK | primarytable1 | fk_pk |
+--------------+-------------+-----------------+-----------------------+------------------------+
1 row in set, 2 warnings (0.03 sec)
마무리
INFORMATION_SCHEMA.KEY_COLUMN_USAGE 뷰를 활용하면 테이블 단위 또는 열 단위로 외래 키 정보를 손쉽게 조회할 수 있습니다. 데이터베이스 구조를 분석하거나 스키마 변경 전에 테이블 간 참조 관계를 파악해야 할 때 매우 유용하게 활용할 수 있습니다.