"ORACLE FK 인덱스"의 두 판 사이의 차이
DB CAFE
1번째 줄: | 1번째 줄: | ||
− | = FK 인덱스 생성쿼리 = | + | == FK DISALBE == |
+ | <source lang=sql> | ||
+ | ALTER TABLE table_name '''DISABLE CONSTRAINT''' constraint_name; | ||
+ | </source> | ||
+ | |||
+ | == FK 인덱스 생성쿼리 == | ||
<source lang=sql> | <source lang=sql> | ||
select 'alter table ' || t1_table_name | select 'alter table ' || t1_table_name |
2018년 10월 19일 (금) 13:29 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 FK DISALBE[편집]
ALTER TABLE table_name '''DISABLE CONSTRAINT''' constraint_name;
2 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')