MySQL에서 열이 기본 키인지 확인하는 방법
특정 열이 기본 키(PRIMARY KEY)로 설정되어 있는지 확인하려면 information_schema.columns 뷰의 COLUMN_NAME과 COLUMN_KEY 값을 활용하면 됩니다. 기본 키로 지정된 열은 COLUMN_KEY 값이 'PRI'로 표시되므로, 이를 CASE 문과 함께 사용하면 각 열이 기본 키인지 아닌지를 한눈에 확인할 수 있습니다.
기본 문법
select column_name,
case when column_key = 'PRI' then 'yourMessage1'
else 'yourMessage2' end as anyAliasName
from information_schema.columns
where table_schema = database()
and `table_name` = yourTableName
order by `table_name`, ordinal_position;
예제 테이블 생성하기
문법을 실제로 이해하기 위해 먼저 샘플 테이블을 생성해 보겠습니다. 아래 테이블은 Id 열을 기본 키로 설정하고 있습니다.
mysql> create table DemoTable1886
(
Id int NOT NULL,
FirstName varchar(20),
LastName varchar(20),
Age int,
DateOfBirth datetime,
Education varchar(40),
PRIMARY KEY(Id)
);
Query OK, 0 rows affected (0.00 sec)
특정 열이 기본 키인지 조회하는 쿼리
이제 위에서 만든 테이블의 각 열이 기본 키인지 여부를 확인하는 쿼리를 실행해 보겠습니다.
mysql> select column_name,
case when column_key = 'PRI' then 'This is a Primary key Column'
else 'This is not a Primary key Column' end as Output
from information_schema.columns
where table_schema = database()
and `table_name` = 'DemoTable1886'
order by `table_name`, ordinal_position;
실행 결과
위 쿼리를 실행하면 다음과 같은 결과가 출력됩니다. Id 열만 기본 키로 표시되고, 나머지 열은 모두 일반 열로 표시되는 것을 확인할 수 있습니다.
+-------------+--------------------------------+
| COLUMN_NAME | Output |
+-------------+--------------------------------+
| Id | This is a Primary key Column |
| FirstName |This is not a Primary key Column|
| LastName |This is not a Primary key Column|
| Age |This is not a Primary key Column|
| DateOfBirth |This is not a Primary key Column|
| Education |This is not a Primary key Column|
+-------------+--------------------------------+
6 rows in set (0.00 sec)
참고: COLUMN_KEY 값의 의미
COLUMN_KEY에는 기본 키 외에도 다양한 인덱스 정보가 저장됩니다. 주요 값은 다음과 같습니다.
- PRI: 기본 키(PRIMARY KEY)로 설정된 열
- UNI: 중복을 허용하지 않는 유니크(UNIQUE) 인덱스가 설정된 열
- MUL: 동일한 값이 여러 번 나타날 수 있는 일반 인덱스가 설정된 열
이처럼 information_schema.columns를 활용하면 별도의 관리 도구 없이 SQL 쿼리만으로 테이블 구조와 키 설정 여부를 손쉽게 파악할 수 있습니다.