1

I am having table friends_list i want to drop its composite primary key(friend_of_id,friends_id_is) and want to create a new composite primary key(friend_of_aid,friends_aid).

for removing primary key i wrote the query 

ALTER TABLE friends_list  DROP PRIMARY KEY

but this is showing following error

#1025 - Error on rename of '.\xrcwrn_sms\#sql-14d4_e0' to '.\xrcwrn_sms\friends_list' (errno: 150)

I am following this post but for composite primary key this is not working My table structure pic is as follow

enter image description here

Community
  • 1
  • 1
xrcwrn
  • 5,339
  • 17
  • 68
  • 129

1 Answers1

2

Check foreign keys on this table, for example using next query -

SELECT
  *
FROM information_schema.REFERENTIAL_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = 'db name' AND REFERENCED_TABLE_NAME = 'your table';

You need to recreate all these foreign keys:

  1. Drop foreign key(s)
  2. Recreate rpimary key
  3. Create foreign key(s)
Devart
  • 119,203
  • 23
  • 166
  • 186
  • I tried query showing error `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REFERENCED_TABLE_NAME = `friends_list` LIMIT 0, 30' at line 4` – xrcwrn Feb 17 '14 at 09:57
  • Add AND keyword between conditions. – Devart Feb 17 '14 at 15:34