Rails modeling: converting HABTM to has_many :through

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?

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.

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.

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.