CODEWITHSUNDEEP
mysql
xrcwrn
xrcwrn

Reputation: 5327

How to delete composite primary key

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

Upvotes: 1

Views: 2205

Answers (1)

Devart
Devart

Reputation: 122032

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)

Upvotes: 2

Related Questions