"ORACLE FK 인덱스"의 두 판 사이의 차이
DB CAFE
(→FK 인덱스 생성쿼리) |
|||
3번째 줄: | 3번째 줄: | ||
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name; | ALTER TABLE table_name DISABLE CONSTRAINT constraint_name; | ||
</source> | </source> | ||
− | + | == FK 조회 == | |
+ | |||
== FK 인덱스 생성쿼리 == | == FK 인덱스 생성쿼리 == | ||
<source lang=sql> | <source lang=sql> |
2018년 10월 19일 (금) 14:00 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 FK DISALBE[편집]
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;
2 FK 조회[편집]
3 FK 인덱스 생성쿼리[편집]
select 'ALTER TABLE ' || t1_table_name
|| ' ADD CONSTRAINT ' || t1_constraint_name
|| ' FOREIGN KEY (' || t1_column_names || ')'
|| ' REFERENCES ' || t2_table_name
|| '(' || t2_column_names || ');' FK_script
from
(select a.table_name t1_table_name
, a.constraint_name t1_constraint_name
, b.r_constraint_name t2_constraint_name
-- Concatenate columns to handle composite
-- foreign keys
, listagg(a.column_name,', ')
within group (order by a.position)
as t1_column_names
from user_cons_columns a
, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.table_name
, a.constraint_name
, b.r_constraint_name
) t1,
(select a.constraint_name t2_constraint_name
, a.table_name t2_table_name
-- Concatenate columns for PK/UK referenced
-- from a composite foreign key
, listagg(a.column_name,', ')
within group (order by a.position)
as t2_column_names
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type in ( 'P', 'U' )
group by a.table_name
, a.constraint_name ) t2
where t1.t2_constraint_name = t2.t2_constraint_name
-- and t2.t2_table_name in ('TB_PG_POG_GDS_DTLS_H')