ORACLE FK 인덱스
DB CAFE
notifications_active 데이터베이스 전문기업 안내
- 데이터 품질 전문기업
http://empathydata.co.kr/
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')