Rails modeling: converting HABTM to has_many :through

Question

I'm doing maintenance work on an existing Rails site and am having some problems stemming from many-to-many associations. It looks like the site was initially built using has_and_belongs_to_many for a few relationships that have since gotten more complicated in the business logic, so I need to use has_many :through instead to support additional fields in the relationship table. However, the join table that was initially used for HABTM doesn't have a primary key, and I've got to add one to support separate relationship modeling using has_many :through.

What's the best way to add a primary key to an existing table with lots of data? Is there another way to do what I'm trying to?

Incidentally, the system is running on Oracle.

Thanks!

Justin

UPDATE 11/9/09 3:58pm: I'm not an Oracle expert and have been getting lost in the wilds of Oracle's versions of not null, auto-increment, and so forth. Initially I tried doing what Mike and Corey recommended by adding a new field as a primary key, but Oracle wouldn't let me add a non-null field to a non-empty table (ORA-01758). I then exported the data as SQL, dropped the rows, added the PK and set it to be non-null, then tried to import the data, but I kept getting errors to the tune of "cannot insert NULL into id..." (ORA-01400).

Finally, I tried using a migration as Corey suggests in his comment, but rake hit the same errors that Oracle was throwing when I altered the database manually ("cannot add non-null field to non-empty table"). I cleared the table, ran the migration (which worked), and then attempted to re-import the data, but I got the same errors last time I'd tried to import ("cannot insert NULL into id..."). How can I save my data and add the primary keys I need? I know that the possibility of writing a rake task was suggested, but I'm unsure as to how to proceed on that front. Any ideas?

Answer 1

I use SQL Developer when administrating an oracle database. Just create the column and add a constraint to the database for example:

sql> alter table Employee add constraint Employee_pk primary key(Employee_ID);

Maybe see here for some more detail.

Edit:

Now that I rethink this you should be able to do it in a migration

add_column :table, :id, :primary_key

You then need to seed some data inside the migration. Just ruby code that iterates through and adds your index. See seed_fu and db-populate for help. Rails 3 will let you seed data with rake db:seed.

Answer 2

Use alter table to add the pk column.

Write a script to set the new pk column values incrementally (some sort of loop).

Once the script is done use alter table again to set the column to primary_key and auto-increment, setting the increment start value to table_size + 1.

Answer 3

You need to create the new column, fill it with the PK values and then create a PK on the new column, eg:

SQL> create table no_pk_tab (c1 varchar2(10), c2 varchar2(10))
Table created.
SQL> insert into no_pk_tab values ('one', 'one')
1 row created.
SQL> insert into no_pk_tab values ('two', 'two')
1 row created.

SQL> alter table no_pk_tab add (id integer)
Table altered.

SQL> create sequence no_pk_seq
start with 1
increment by 1
Sequence created.

SQL> update no_pk_tab set id = no_pk_seq.nextval
2 rows updated.
SQL> select * from no_pk

C1         C2             PK_COL
---------- ---------- ----------
one        one                 1
two        two                 2

2 rows selected.

SQL> alter table no_pk add primary key (pk_col) using index
Table altered.

Depending on how many rows are in your table it may take a while to populate the sequence values, but it will work.