[ORACLE] 제약 조건 관리 쿼리 모음

업무를 진행하며 제약 조건명을 수정하거나 생성하는 경우가 필요했다. 이 포스트에서는 제약 조건 명을 조회, 수정, 생성하는 쿼리를 정리했다.

테이블 설명

• USER_CONSTRAINTS: 유저가 소유한 모든 제약 조건을 볼 수 있음
• CONSTRAINT_TYPE:
  - R: FK (FOREIGN KEY)
  - C: CHECK, NOT NULL
  - P: PK (PRIMARY KEY)
  - U: UNIQUE
• USER_CONS_COLUMNS : 컬럼에 할당된 제약 조건을 볼 수 있음
• USER_INDEXES: 유저가 소유한 인덱스를 볼 수 있음
• USER_IND_COLUMNS: 인덱스 컬럼을 볼 수 있음

PK (Primary Key) 조회 및 수정

• PK명 조회

SELECT 
    A.TABLE_NAME,
    A.CONSTRAINT_NAME,
    B.COLUMN_NAME
FROM USER_CONSTRAINTS A
JOIN USER_CONS_COLUMNS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'P'
ORDER BY A.TABLE_NAME;

• PK명 수정 쿼리

SELECT
    'ALTER TABLE "' || A.TABLE_NAME || '" RENAME CONSTRAINT "' || A.CONSTRAINT_NAME || '" TO "PIX_' || A.TABLE_NAME || '_PK";' AS TN
FROM USER_CONSTRAINTS A
WHERE A.CONSTRAINT_TYPE = 'P'
    AND A.TABLE_NAME NOT LIKE 'BIN%' -- 'BIN%'은 테이블이나 제약조건이 삭제되었지만, 휴지통에 남아있는 상태
ORDER BY A.TABLE_NAME;

FK (Foreign Key) 조회 및 수정

• FK명 조회

SELECT 
    A.TABLE_NAME,
    A.CONSTRAINT_NAME, 
    LISTAGG(B.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY B.POSITION) AS COLUMNS,
    C.TABLE_NAME AS REFERENCED_TABLE_NAME,
    LISTAGG(D.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY D.POSITION) AS REFERENCED_COLUMNS
FROM USER_CONSTRAINTS A
JOIN USER_CONS_COLUMNS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
LEFT JOIN USER_CONSTRAINTS C ON A.R_CONSTRAINT_NAME = C.CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS D ON C.CONSTRAINT_NAME = D.CONSTRAINT_NAME AND B.POSITION = D.POSITION
WHERE A.CONSTRAINT_TYPE = 'R'
GROUP BY A.TABLE_NAME, A.CONSTRAINT_NAME, C.TABLE_NAME
ORDER BY A.TABLE_NAME;

• FK명 수정 쿼리

SELECT 
    'ALTER TABLE ' || A.TABLE_NAME || ' ADD CONSTRAINT "' || A.CONSTRAINT_NAME || '" FOREIGN KEY (' 
    || LISTAGG(B.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY B.POSITION) || ') REFERENCES ' || C.TABLE_NAME 
    || '(' || LISTAGG(D.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY D.POSITION) || ');' AS TB
FROM USER_CONSTRAINTS A
JOIN USER_CONS_COLUMNS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
LEFT JOIN USER_CONSTRAINTS C ON A.R_CONSTRAINT_NAME = C.CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS D ON C.CONSTRAINT_NAME = D.CONSTRAINT_NAME AND B.POSITION = D.POSITION
WHERE A.CONSTRAINT_TYPE = 'R'
GROUP BY A.TABLE_NAME, A.CONSTRAINT_NAME, C.TABLE_NAME
ORDER BY TB;

NOT NULL 조회 및 수정

• NOT NULL 조회

SELECT 
    UC.TABLE_NAME,
    UC.CONSTRAINT_NAME,
    UCC.COLUMN_NAME 
FROM USER_CONSTRAINTS UC
JOIN USER_CONS_COLUMNS UCC ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
WHERE UC.CONSTRAINT_TYPE = 'C'
    AND UC.TABLE_NAME NOT LIKE 'BIN%'
ORDER BY UC.TABLE_NAME;

• NOT NULL명 수정 쿼리

SELECT 
    'ALTER TABLE "' || UC.TABLE_NAME || '" RENAME CONSTRAINT "' || UC.CONSTRAINT_NAME || '" TO "' 
    || UC.TABLE_NAME || '_' || UCC.COLUMN_NAME || '_NOT_NULL";' AS RENAME_SQL
FROM USER_CONSTRAINTS UC
JOIN USER_CONS_COLUMNS UCC ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
WHERE UC.CONSTRAINT_TYPE = 'C'
    AND UC.TABLE_NAME NOT LIKE 'BIN%'
ORDER BY UC.TABLE_NAME;

인덱스 조회 및 생성

• 인덱스 조회

SELECT 
    UI.INDEX_NAME,
    UI.TABLE_NAME,
    LISTAGG(UIC.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY UIC.COLUMN_POSITION) AS COLUMNS
FROM USER_INDEXES UI
JOIN USER_IND_COLUMNS UIC ON UI.INDEX_NAME = UIC.INDEX_NAME
WHERE UI.INDEX_NAME LIKE 'IDX%'
GROUP BY UI.INDEX_NAME, UI.TABLE_NAME
ORDER BY UI.TABLE_NAME;

• 인덱스 생성 쿼리

SELECT 
    'CREATE INDEX ' || UI.INDEX_NAME || ' ON ' || UI.TABLE_NAME || ' (' || 
    LISTAGG(UIC.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY UIC.COLUMN_POSITION) || 
    ') TABLESPACE TABLESPACE_NM' AS IDX
FROM USER_INDEXES UI
JOIN USER_IND_COLUMNS UIC ON UI.INDEX_NAME = UIC.INDEX_NAME
WHERE UI.INDEX_NAME LIKE 'IDX%'
GROUP BY UI.INDEX_NAME, UI.TABLE_NAME
ORDER BY UI.TABLE_NAME;