Enterprise Rails

Refactor Steps

1. Examine each table. Are there any non-primary key columns that have a unique constraint, or which should have a unique constraint?

2. If you found places where you must add a unique constraint, add it:

   create unique index concurrently table_name_column_one_column_two_uniq_idx
     on table_name(column_one, column_two, ...);

3. Choose the next steps based on whether your unique constraint is based on one column or multiple columns.

Single Column Refactor

1. In the table’s model class, change the primary key column using set_primary_key:

   set_primary_key :unique_column

2. In tables that reference this table, add a column to reference the new key:

   alter table dependent_table
     add column referenced_table_unique_column coltype;

3. Fill the column with appropriate values:

   update dependent_table
     from referenced_table r
      set referenced_table_unique_column = r.unique_col
    where referenced_table_id = r.id;

4. Add the foreign key reference constraint to each dependent table:

   alter table dependent_table
     add constraint referenced_table_unique_column_fkey
        (referenced_table_unique_col)
     references referenced_table(unique_col);

5. Drop the original id column and reference:

   alter table referenced_table
    drop column id;
   alter table dependent_table
    drop column referenced_table_id;

6. Set the new column as the primary key:

   alter table referenced_table
     add primary key(unique_column);

Multiple Column Refactor

1. Install the composite_primary_keys gem:

   gem install composite_primary_keys

2. Load the gem in your application. In environment.rb, add ...