행위

ORACLE FK 인덱스

DB CAFE

Dbcafe (토론 | 기여)님의 2019년 12월 19일 (목) 11:14 판
(차이) ← 이전 판 | 최신판 (차이) | 다음 판 → (차이)
thumb_up 추천메뉴 바로가기


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')