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