DataBase > Oracle/TiberoOracle DDL 제약
#tibero
USER_CONSTRAINTS, USER_CONS_COLUMNS
1. USER_CONSTRAINTS
1) 제약조건 명 (CONSTRAINT_NAME)
2) 제약조건 유형 (CONSTRAINT_TYPE)
- (P) PRIMARY KEY
- (R) FOREIGN KEY
- (U) UNIQUE
- (C) CHECK, NOT NULL
3) 제약조건이 속한 테이블명 (TABLE_NAME)
2. 제약조건 및 컬럼 확인
SELECT
UC.TABLE_NAME
, COLUMN_NAME
, CONSTRAINT_TYPE
, UC.CONSTRAINT_NAME
FROM
USER_CONSTRAINTS UC
JOIN USER_CONS_COLUMNS UCC ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
WHERE
UC.TABLE_NAME = UPPER('테이블명');
3. 부자관계의 모든 테이블들 출력
SELECT
AC1.OWNER
, AC1.CONSTRAINT_NAME
, AC2.TABLE_NAME AS PARENT_TABLE
, AC1.TABLE_NAME AS CHILD_TABLE
FROM
ALL_CONSTRAINTS AC1
, ALL_CONSTRAINTS AC2
WHERE
AC1.R_CONSTRAINT_NAME = AC2.CONSTRAINT_NAME
AND AC1.CONSTRAINT_TYPE = 'R'
ORDER BY AC1.TABLE_NAME;
4. (테이블명)을 참조하는 모든 테이블 목록 출력(자식 테이블 목록 출력)
SELECT
AC1.OWNER
, AC1.CONSTRAINT_NAME
, AC1.TABLE_NAME
FROM
ALL_CONSTRAINTS AC1
, ALL_CONSTRAINTS AC2
WHERE
AC1.R_CONSTRAINT_NAME = AC2.CONSTRAINT_NAME
AND AC1.CONSTRAINT_TYPE = 'R'
AND AC2.TABLE_NAME = UPPER('테이블명')
ORDER BY AC1.TABLE_NAME;
5. (테이블명)이 참조하고 있는 모든 체이블 목록 출력(부모 테이블 목록 출력)
SELECT
TABLE_NAME
FROM
USER_CONSTRAINTS
WHERE
CONSTRAINT_NAME IN (
SELECT
R_CONSTRAINT_NAME
FROM
USER_CONSTRAINTS
WHERE
TABLE_NAME = UPPER('테이블명')
AND CONSTRAINT_TYPE = 'R'
);
*** 제약 조건 확인
SELECT
CONSTRAINT_NAME
, TABLE_NAME
, R_CONSTRAINT_NAME
, CONSTRAINT_TYPE
, SEARCH_CONDITION
FROM USER_CONSTRAINTS;