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?
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.