CODEWITHSUNDEEP
mysqlprimary-keyinnodbmysql-error-1025
Steven Huwig
Steven Huwig

Reputation: 20784

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.

Upvotes: 0

Views: 289

Answers (2)

Miguel L.
Miguel L.

Reputation: 738

Add the e column to the bar table and then update the primary key by issuing the following MySQL specific command :

ALTER TABLE bar DROP PRIMARY KEY, ADD PRIMARY KEY (a, c, e);

Upvotes: 0

Mike Mytkowski
Mike Mytkowski

Reputation: 596

This other stackoverflow question might help you out.

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

Upvotes: 1

Related Questions