Search code examples
mysqlprimary-keyinnodbmysql-error-1025

How can I add a column to the primary key of a MySQL InnoDB table?

I have tables foo and bar:

create table foo(a int, b varchar(10), 
                 primary key (a));
create table bar(a int, c int, d int,
                 primary key (a,c),
                 foreign key(a) references foo(a));

Now I have a new column e that needs to participate in the primary key of bar. How can I do this? It seems I should be able to drop the primary key, add the column, and create a new primary key, but attempting to drop the primary key gives me:

mysql> alter table bar drop primary key;
ERROR 1025 (HY000): Error on rename of './mydb/#sql-1e08_16a273' to './mydb/bar' (errno: 150)

This only appears to be the case with primary keys that include a foreign key column.

Solution

  • This other stackoverflow question might help you out.

    My guess would be you need to first drop the foreign key, then drop the primary.