[SQL] 제약조건 확인하기 (딕셔너리 활용)

출처 : http://charming-jung.tistory.com/28?category=782855

 

제약 조건 확인하기

SQL문을 작성하다 보면 무결성 제약 조건에 위배되는 경우가 발생하는데, 이 때 어떤 무결성 제약 조건에 위배되었는지 알기란 쉬운 일이 아니다. 그래서 제약 조건(CONSTRAINTS)의 에러 메시지에 대한 정확한 원인을 알기 어려울 때, 오라클에서 제공해주는 USER_CONSTRAINTS 데이터 딕셔너리를 살펴 보아야 한다.

USER_CONSTRAINTS 데이터 딕셔너리는 제약 조건의 정보를 위해 많은 칼럼으로 구성되어 있다. 그 중에서 몇가지만 알아보자.

1. 제약 조건명(칼럼명 : CONSTRAINT_NAME)

2. 제약 조건 유형(칼럼명 : CONSTRAINT_TYPE)

3. 제약 조건이 속한 테이블명(칼럼명 : TABLE_NAME)

※ 칼럼의 크기 변경

칼럼 이름인 CONSTRAINT_TYPE은 저장되는 데이터가 1자이기에 3칸으로 출력된다.

따라서 칼럼제목이 다 보이도록 하려면 CONSTRAINT_TYPE 칼럼의 크기를 늘려 주어야 한다.

출력되는 칼럼의 크기를 늘려 주기 위해서는 'COLUMN CONSTRAINT_TYPE FORMAT A칼럼크기' 명령어를 입력하면 된다.

CONSTRAINT_TYPE은 P, R, U, C 4가지 값 중에 하나를 갖는다.

CONSTRAINT_TYPE	의     미
P	PRIMARY KEY
R	FOREIGN KEY
U	UNIQUE
C	CHECK, NOT NULL

무결성 제약조건이 5개인데 제약 조건 유형이 4가지인 이유는 NOT NULL과 CHECK 제약 조건을 모두 C로 표현하기 때문이다. NOT NULL 제약 조건은 칼럼에 NOT NULL 조건을 체크할지 말지를 결정하기 때문에 CHECK를 나타내는 C로 표현한 것으로 이해하면 된다.

또, 각각의 제약 조건 유형은 제약 조건의 이니셜로 표현되지만 FOREIGN KEY만은 A로 표현된다. 이는 FOREIGN KEY는 참조의 무결성을 지켜야 하기에 참조(REFERENCE) 무결성(INTEGRITY)의 이니셜인 R을 FOREIGN KEY의 제약 조건 유형으로 지정한 것으로 이해하면 된다.

그런데 USER_CONSTRAINTS은 출력된 제약 조건이 어느 칼럼에 정의되었는지 칼럼명이 보이질 않는다.

그렇다면 어떤 칼럼에 해당 제약 조건이 지정되었는지 알려주는 데이터 딕셔너리는 없을까?

어떤 칼럼에 해당 제약 조건이 지정되었는지 알려주는 데이터 딕셔너리

--> USER_CONS_COLUMNS 데이터 딕셔너리는 제약 조건이 지정된 칼럼명도 저장한다.

출력 내용을 보면 유저명이 XERXES, 제약 조건 이름이 FK_AA, 테이블명은 AA, 컬럼명이 ID, POSITION은 1 이다.

제약 조건이 지정되어있는 컬럼들을 모두 출력한다. (현재는 만든 제약조건이 한개 밖에없어서 한개만 출력됨)

위의 COLUMN 칼럼명 FORMAT A크기 명령어를 활용하여 각각의 컬럼명들의 크기를 변경하면 깔끔하게 출력할 수 있다.

문자의 경우에는 FORMAT뒤에 A를 쓰고 길이를 지정해주는 반면 숫자의 경우에는 컬럼의 데이터값들의 자리수만큼 9로 설정해줘야 한다. 그렇지 않으면 아래의 결과와 같이 #로 값이 출력된다.

따라서 USER_CONSTRAINTS 데이터 딕셔너리와 USER_CONS_COLUMNS 데이터 딕셔너리를 함께 살펴보면 어떤 칼럼에 어떤 제약 조건이 지정되었는지 확인할 수 있다.