행위

ORACLE FK 인덱스

DB CAFE

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