데이터베이스를 관리하다 보면 특정 스키마에 정의된 PRIMARY KEY, UNIQUE, FOREIGN KEY 같은 제약 조건들이 어떤 테이블의 어떤 컬럼에 적용되어 있는지 한눈에 파악해야 할 때가 많습니다.
예를 들어 여러 개의 테이블을 가진 business라는 데이터베이스가 있다고 가정해 보겠습니다. 이 데이터베이스의 각 제약 조건에 포함된 필드(컬럼) 정보를 확인하려면 MySQL의 시스템 뷰인 information_schema.key_column_usage를 조회하면 됩니다.
제약 조건별 필드 조회 쿼리
아래 쿼리는 business 데이터베이스에 속한 모든 제약 조건과 해당 컬럼 정보를 가져옵니다.
mysql> SELECT *
-> FROM information_schema.key_column_usage
-> WHERE constraint_schema = 'business';constraint_schema 조건에 원하는 데이터베이스 이름을 지정하면, 해당 스키마 전체의 제약 조건 정보를 한 번에 조회할 수 있습니다.
실행 결과
쿼리를 실행하면 아래와 같은 형태의 결과가 출력됩니다. (실제 결과는 총 52행이 반환되었으며, 아래는 주요 행만 발췌한 것입니다.)
+--------------------+-------------------+--------------------------+---------------+--------------+------------------------------+--------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | +--------------------+-------------------+--------------------------+---------------+--------------+------------------------------+--------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | def | business | PRIMARY | def | business | primarytable | FKPK | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | autoincrementtable | id | 1 | NULL | NULL | NULL | NULL | | def | business | name | def | business | uniquedemo | name | 1 | NULL | NULL | NULL | NULL | | def | business | id | def | business | uniquedemo1 | id | 1 | NULL | NULL | NULL | NULL | | def | business | id | def | business | uniquedemo1 | name | 2 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | compositeprimarykey | Id | 1 | NULL | NULL | NULL | NULL | | def | business | PRIMARY | def | business | compositeprimarykey | StudentName | 2 | NULL | NULL | NULL | NULL | | def | business | constFKPK | def | business | foreigntable | Fk_pk | 1 | 1 | business | primarytable1 | fk_pk | | def | business | FKConst | def | business | foreigntabledemo | FK | 1 | 1 | business | primarytabledemo | fk | | def | business | StudCollegeConst | def | business | studentenrollment | StudentFKPK | 1 | 1 | business | college | studentfkpk | | def | business | primarytable1demo_ibfk_1 | def | business | primarytable1demo | ForeignId | 1 | 1 | business | foreigntable1 | studentid | +--------------------+-------------------+--------------------------+---------------+--------------+------------------------------+--------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ 52 rows in set, 2 warnings (0.21 sec)
결과 컬럼 의미 살펴보기
- CONSTRAINT_SCHEMA: 제약 조건이 속한 데이터베이스(스키마) 이름입니다.
- CONSTRAINT_NAME: 제약 조건의 이름입니다. 기본 키는
PRIMARY, 외래 키는 자동 생성 이름(예:_ibfk_1) 또는 사용자 지정 이름으로 표시됩니다. - TABLE_NAME / COLUMN_NAME: 해당 제약 조건이 적용된 테이블과 컬럼입니다.
- ORDINAL_POSITION: 복합 키처럼 여러 컬럼이 하나의 제약 조건에 포함된 경우, 컬럼의 순서를 나타냅니다. 위 예제의
compositeprimarykey테이블처럼Id(1번)와StudentName(2번)이 하나의 기본 키를 구성합니다.
외래 키(Foreign Key) 식별 방법
일반적인 기본 키나 유니크 제약 조건은 POSITION_IN_UNIQUE_CONSTRAINT 이후의 참조 관련 컬럼이 모두 NULL로 표시됩니다. 반면 외래 키인 경우에는 다음과 같이 참조 정보가 함께 출력됩니다.
- POSITION_IN_UNIQUE_CONSTRAINT: 참조되는 유니크 키 내에서의 컬럼 위치
- REFERENCED_TABLE_SCHEMA: 참조 대상 테이블이 있는 스키마
- REFERENCED_TABLE_NAME: 참조되는 부모 테이블 이름
- REFERENCED_COLUMN_NAME: 참조되는 부모 테이블의 컬럼 이름
예를 들어 foreigntable 테이블의 Fk_pk 컬럼은 business 스키마의 primarytable1 테이블에 있는 fk_pk 컬럼을 참조한다는 것을 바로 알 수 있습니다.
정리
information_schema.key_column_usage 뷰를 활용하면 복잡한 스키마에서도 제약 조건과 컬럼 간의 관계를 빠르게 파악할 수 있습니다. 특히 외래 키 참조 관계를 문서화하거나, 데이터베이스 구조를 분석·점검할 때 매우 유용하게 사용할 수 있습니다.