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.