Oracle pk fk조회 - oracle pk fkjohoe

Question

[데이터베이스 목차]

안녕하세요 양햄찌 블로그 주인장 입니다.

업무하다 보면 자주 사용하는 쿼리들이 있죠,

테이블 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_CONSTRAINTS 테이블 - SQL DEVELOPER 실습화면

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 필드를 참고하면 됩니다.

오늘은 간단하게 오라클에서 키를 포함한 제약조건 정보를 조회하는 방법에 대해 알아봤어요~!

공감은 늘 힘이됩니다 :) 다음 포스팅에서 봐요.

DB/oracle

2020. 11. 17. 23:24

  SELECT AA.COLUMN_ID,
         AA.COLUMN_NAME,
         BB.COMMENTS,
         AA.DATA_TYPE,
         AA.DATA_DEFAULT,
         CC.PK,
         AA.NULLABLE,
         CC.FK
    FROM ALL_TAB_COLUMNS AA,
         ALL_COL_COMMENTS BB,
         (SELECT A.OWNER,
                 A.TABLE_NAME,
                 A.CONSTRAINT_TYPE,
                 COLUMN_NAME,
                 POSITION,
                 CASE WHEN A.CONSTRAINT_TYPE = 'P' THEN 'Y' END AS PK,
                 CASE WHEN A.CONSTRAINT_TYPE = 'R' THEN 'Y' END AS FK
            FROM ALL_CONSTRAINTS A, 
            ALL_CONS_COLUMNS B
           WHERE     UPPER (A.OWNER) = UPPER ('owner')
                 AND A.TABLE_NAME = UPPER ('{{테이블명}}')
                 AND A.TABLE_NAME = B.TABLE_NAME
                  AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
                 AND A.CONSTRAINT_TYPE IN ('P', 'F')) CC
   WHERE     UPPER (AA.OWNER) = UPPER ('owner')
         AND UPPER (AA.TABLE_NAME) = UPPER ('{{테이블명}}')
         AND AA.OWNER = BB.OWNER
         AND AA.TABLE_NAME = BB.TABLE_NAME
         AND AA.COLUMN_NAME = BB.COLUMN_NAME
         AND AA.OWNER = CC.OWNER(+)
         AND AA.TABLE_NAME = CC.TABLE_NAME(+)
         AND AA.COLUMN_NAME = CC.COLUMN_NAME(+)
ORDER BY COLUMN_ID

[PK 삭제 방법]
- ALTER TABLE EMP_TAB DROP PRIMARY KEY;

[PK 생성 방법]
- ALTER TABLE EMP_TAB ADD PRIMARY KEY (필드1, 필드2, 필드3));
- ALTER TABLE EMP_TAB ADD CONSTRAINT EMP_PK(PK이름) PRIMARY KEY (필드1, 필드2, 필드3));

[FK 삭제 방법]
- ALTER TABLE EMP1 DROP CONSTRAINT EMP1_FK;

[FK 생성 방법]
- ALTER TABLE EMP1 ADD CONSTRAINT EMP1_FK(FK이름) FOREIGN KEY(필드1, 필드2, 필드3)
REFERENCES EMP2(필드1, 필드2, 필드3);

[PK,FK 조회]

SQL> select c.constraint_name as "foreign key",
p.constraint_name as "referenced key",
p.constraint_type,
p.owner,
p.table_name
from dba_constraints c, dba_constraints p
where c.owner = 'SCOTT'
and c.table_name = 'EMP'
and c.constraint_type = 'R'
and c.r_owner = p.owner
and c.r_constraint_name = p.constraint_name;