Issues

Issue #106 on hold

Cannot rename primary key columns

dwt
created an issue

I would have expected to do alter_column(... existing_primary_key=True), but that does not exist.

Some research in the web suggests that it is currently not possible to rename a primary key column with alembic - which currently bites us, as we have taken over an application which works against an old database and would now like to slowly unify its names, to make it easier to work with.

Comments (4)

  1. Mike Bayer repo owner

    need more detail on this. On a database such as Postgresql there is no requirement that the primary key constraint be also altered when the name of a column is changed - column renames need not reference primary key status. Usually MySQL is the offender here, but even on their docs http://dev.mysql.com/doc/refman/5.1/en/alter-table.html it seems the primary key attribute need not be mentioned:

    When you use CHANGE or MODIFY, column_definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE.

    note that for MySQL we are needing to rework its whole system in any case which is #103.

  2. dwt reporter

    This was with the mysql backend, so indeed it is the offender.

    The sql looked something like this

    mysql> show create table roles;
    +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                                                                                                                     |
    +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | roles | CREATE TABLE `roles` (
      `idusergroup` int(11) NOT NULL AUTO_INCREMENT,
      `title` varchar(128) DEFAULT NULL,
      PRIMARY KEY (`idusergroup`),
      UNIQUE KEY `title` (`title`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
    +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    

    The problem here seems to me that the primary key constraint is declared in its own line - though of course I'm not sure that is a problem.

    What I do know is that renaming the column with alembic failed.

  3. Mike Bayer repo owner

    OK, well its likely #103 will get this, but let's get some detail on what "failed" means. Basic rename of a column in the MySQL dialect should come out like this:

    ALTER TABLE t1 CHANGE c1 c2 INTEGER NOT NULL
    

    so..did that succeed? was it just that the PK constraint was lost or was there some kind of error message ?

  4. Log in to comment