예제를 오라클의 기본 SCOTT 계정에 있는 DEPT테이블로 테스트 했습니다.
# 특정 테이블에서 인덱스 걸린 컬럼 확인하기. 테이블명은 대소문자를 구별합니다.
PK는 자동 인덱싱이므로 당연히 PK가 있는 테이블이라면 1개는 나올 것이다.
SELECT *
FROM ALL_IND_COLUMNS
WHERE TABLE_NAME = [테이블명]
# 예제
sql> SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME = 'DEPT'
# PK확인하기. 테이블명은 대소문자를 구분합니다.
SELECT *
FROM USER_CONS_COLUMNS
WHERE CONSTRAINT_NAME LIKE 'PK%'
AND TABLE_NAME = [테이블명]
ORDER BY TABLE_NAME
# 예제
sql> SELECT *
FROM USER_CONS_COLUMNS
WHERE CONSTRAINT_NAME LIKE 'PK%'
AND TABLE_NAME = 'DEPT'
ORDER BY TABLE_NAME
SELECT A.TABLE_NAME, B.COLUMN_NAME, A.CONSTRAINT_NAME, A.CONSTRAINT_TYPE, A.SEARCH_CONDITION
FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.TABLE_NAME = '테이블명'
AND A.CONSTRAINT_TYPE = 'P';
컨텐츠로 건너뛰기
SELECT * FROM USER_CONS_COLUMNS UCC INNER JOIN USER_CONSTRAINTS UC
ON UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
WHERE UC.CONSTRAINT_TYPE = 'P'
* 각 테이블별 PK를 한 컬럼에 표시 * 각 테이블별 PK를 한 컬럼에 표시2. MS-SQL
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME='[테이블명]'
[데이터베이스 목차]
안녕하세요 양햄찌 블로그 주인장 입니다.
업무하다 보면 자주 사용하는 쿼리들이 있죠,
테이블 insert할 때 failed to append record 무결성 제약조건이 위배되었습니다 - 부모키가 없습니다.
이런 에러들이 나면, 주키가 무엇인지 먼저 확인해줘야 해요. 머 이 외에도 제약조건이나 키를 확인해줘야 할 사항이 종종 꽤 있습니다.
오라클은 아쉽게도 커맨드 desc나
테이블 정보를 확인하는 alt+c에서 기본적인 주키, 외래키 등의 정보를 확인할 수 없습니다.
그래서 필요한 상황에 쿼리를 통해 테이블에서 조회해줘야 해요.
오늘은 테이블의 각 키 정보들, 제약 조건 정보들을 테이블에서 확인하는 방법에 대해 포스팅할게요.
테이블의 기본키(주키)찾는 쿼리
select a.owner 계정, a.table_name 테이블명, a.constraint_type 제약조건종류, b.column_name 컬럼명 from all_constraints a, all_cons_columns b where a.constraint_name = b.constraint_name and a.table_name = UPPER('테이블명') and a.constraint_type = 'P' --기본키(Primary Key)쿼리만 필요하신 분들을 위해서~! 위의 쿼리로 검색하시면 됩니다.
HR 실습 테이블에서 EMPLOYEES의 기본키를 찾아봤어요 ㅎㅎ
ALL_CONSTRAINTS 테이블
일단 제약조건을 확인할 수 있는 테이블명으로는 all_constraints가 있습니다.
그런데 all_constraints는 주키인지 외래키인지, 제약조건명은 무엇인지, 제약조건에 대한 정보를 담고 있지만, 컬럼에 대한 정보는 없습니다. 제약조건명과 그에 따른 정보들만 있을 뿐이예요.
all_constaraints 테이블의 컬럼목록
OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION, SEARCH_CONDITION_VC, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED, GENERATED, BAD, RELY, LAST_CHANGE, INDEX_OWNER, INDEX_NAME, INVALID, VIEW_RELATED, ORIGIN_CON_ID
모든 컬럼명에 대한 자세한 설명을 알고싶으신 분은 오라클 document 설명을 참고하세요!
테이블명(TABLE_NAME)과 제약조건 종류(CONSTRAINT_TYPE)에 대한 정보는 있는데 컬럼명은 없는걸 확인할 수 있어요.
ALL_CONS_COLUMNS 테이블
그래서 제약조건명을 모르고, 내가 조회하고 싶은 테이블의 컬럼에 걸려있는 제약조건을 확인하고 싶으면
all_cons_columns라는 테이블과 조인에서 함께 확인해주는 것이 좋아요.
all_cons_columns 테이블에는 제약조건 종류에 관한 정보는 없으나 대신 컬럼명(COLUMN_NAME)이 있습니다!
제약조건 종류에 따른 코드 값 - CONSTRAINT_TYPE
주키 말고 외래키 등 다른 제약조건을 검색하고 싶을 수 있겠죠
P는 주키, R은 외래키, U 유니크, C 체크, V 뷰체크,
| 값 | 의미 |
| P | 주키, 기본키 (PK) |
| R | 외래키, 포린키(Foreign Key = FK) |
| U | Unique Key 유니크 키 |
| C | Check 체크 ( 입력되는 값 체크하는 제약조건 ex. NOT NULL) |
| V | Check Option on a view ( 뷰에서 체크) |
| O | Read Only on a view |
외래키의 경우 어떤 다른 테이블 참조에 걸려있는지 확인하고 싶을 경우 all_constraints 테이블의 R_CONSTRAINT_NAME 필드를 참고하면 됩니다.
오늘은 간단하게 오라클에서 키를 포함한 제약조건 정보를 조회하는 방법에 대해 알아봤어요~!
공감은 늘 힘이됩니다 :) 다음 포스팅에서 봐요.