[ISSUE] When removing PK column

General Discussion
1

Hi all,

I’ve got a table with a column A that is PK.
I want to remove that column and create a new one, named B, that would become the PK.

I used the diffChangeLog command to automatically generate the ChangeLog and apply it on the fly (I’m creating a workflow involving reworking a DB from Hibernate code).
The changeLog propose:

  • insert new column B
  • add PK on B (that fails)
  • drop PK on table X

Which crashs on MySQL as we can’t deal with two PK at the same time…

Regards,

2

Hi,

It seems it is more complicated than expected. Apparently, there is a trick with chained PK/FK… Can’t even deal with it manually. Gasp.

3

I had to do this before once. But i used an application level export/import.

If you want to do that on DB level that probably means dropping the existing PF and FK constraints, creating the new colums and filling them, dropping the old columns and then creating the constraints using the new columns again.

4

Moving primary keys is difficult, you may need to modify the generated changesets to use tags since I’m not sure how well the generated code will work.  Besides the the export/import trick from ekupcik.  It can sometimes be done faster with a “select into” statement rather than a full export/import. 

  1. Create your new table structure as a brand new table,
  2. Do a select into from the old table to the new
  3. Modify all FKs that point to the old table to point to the new
  4. Drop old table
  5. Rename new table to old table name

Otherwise, you may be able to drop the old PKs first before creating the new PKs, but you will need to remove all FKs pointing to the table first.

Nathan