Re-assigning Foreign Keys

The Problem

We had a number of duplicates in our contacts tables (I'm sure we're not unique there!) and I built a little application that allowed people to identify them. The next problem was to safely remove the duplicate. I started by hard coding a script to change a list of named foreign keys in the database that referenced the duplicate to now reference the original. This worked well, but I realised that I was building a maintenance overhead: future tables which referenced the contacts tables (and these seem to be popular tables) would have to be manually added or the remove duplicates application would fail.

The Solution

I realised that if I searched ALL_CONSTRAINTS to for references rather than hard-coding them then I could future-proof my data cleaning application. My job was made much simpler because all our tables have a single numeric primary key. Well, almost all, but that's another story.

I wrapped the code in a function so I could return a status report, and then put it in a package because I'm sure I will need to write other utilities and its neater to keep them together.

You can download the package header and the package body, but I've included the package body below:

function move_fk(p_table_name in varchar2, p_old_id in number, p_new_id in number) 
  return number 
as
  lv_return number;
  -- return value = meaning
  --      0       = no problem, just no references to the old_id.
  --     -1       = old_id was invalid
  --     -2       = new_id was invalid
  --     -3       = table_name was invalid
  --     -4       = other problem encountered
  lv_sql varchar2(1000);
  lv_pk varchar2(50);
  TYPE cur_typ IS REF CURSOR;
  c_cursor cur_typ;
  lv_table varchar2(50);
  lv_col varchar2(50);
  lv_debug varchar2(1000);
  
  lv_count number := 0;
  
begin
  lv_return := null;

  -- Check the table name is valid
  select count(*) into lv_count from all_tables where table_name = upper(p_table_name);
  if lv_count != 1 then
    -- either no tables or many, either of which is a problem
    lv_return := -3;
  else
    -- valid table name
    -- get the name of the primary key
    SELECT cols.column_name
      into lv_pk
      FROM all_constraints cons, all_cons_columns cols
      WHERE cons.constraint_type = 'P'
      AND cons.constraint_name = cols.constraint_name
      and cols.table_name = upper(p_table_name);
    
    -- now check old_id
    execute immediate 'select count(*) from ' || p_table_name || 
      ' where ' || lv_pk || ' = ' || p_old_id
      into lv_count; 
    if lv_count != 1 then
      -- either none or many rows matching old_id
      lv_return := -1;
    else
      -- valid old_id
      -- now check new_id
      execute immediate 'select count(*) from ' || p_table_name  || 
      ' where ' || lv_pk || ' = ' || p_new_id
      into lv_count; 
      if lv_count != 1 then
        -- either none or many rows matching old_id
        lv_return := -2;
      else
        -- valid new_id
        -- now we can do it
        -- first build the query to select the table and fk column names
        lv_sql := 'SELECT cols.table_name, cols.column_name '||
          'FROM all_constraints cons, all_cons_columns cols ' ||
          'WHERE cons.constraint_type = ''R'' ' ||
          'AND cons.constraint_name = cols.constraint_name ' ||
          'AND cons.owner = cols.owner ' ||
          'and cons.r_constraint_name in ' ||
          '(select constraint_name from all_constraints ' ||
            ' where constraint_type = ''P'' ' ||
            ' and table_name = ''' || upper(p_table_name) || ''')';
            

        -- now open the query
        OPEN c_cursor FOR lv_sql;
        -- loop through the results
        LOOP
          FETCH c_cursor INTO lv_table, lv_col;
          EXIT WHEN c_cursor%NOTFOUND;
          -- process row here
          execute immediate 'update ' || lv_table || 
            ' set ' || lv_col || ' = ' || p_new_id ||
            ' where ' || lv_col || ' = ' || p_old_id;
        END LOOP;
        CLOSE c_cursor;

        commit;
        
      end if;
    end if;
  end if;

  return lv_return;
exception
  when others then
    lv_debug := sqlerrm;
    return -4;
end move_fk;

It would not be too difficult to overload this function to handle VARCHAR2 keys. It would be more difficult to deal with keys made up of multiple parts (yet another good reason for avoiding them in my opinion).