0

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Steven Huwig
  • 20,015
  • 9
  • 55
  • 79

2 Answers2

1

This other stackoverflow question might help you out.

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

Community
  • 1
  • 1
Mike Mytkowski
  • 596
  • 2
  • 8
0

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);
Miguel L.
  • 708
  • 2
  • 7
  • 11