CODEWITHSUNDEEP
mysqlsqlindexingforeign-keysrelation
cyrfandli
cyrfandli

Reputation: 249

How to drop a foreign key from a table?

I accidentally made a foreign key with a primary within a table. I would like to delete the Index but if I try, it gives me: "Cannot drop index "admin_id" needed in a foreign key constraint."

How can I delete the relation?

Upvotes: 0

Views: 191

Answers (1)

user2864740
user2864740

Reputation: 61865

Drop the the FOREIGN KEY CONSTRAINT first - this relational constraint is established from the foreign table. Dropping a FK constraint does not drop or alter any column!

ALTER TABLE foreign_table DROP FOREIGN KEY fk_constraint_name

Once there are no more constraints the KEY (e.g. INDEX, PK) status or entire column can be removed.

ALTER TABLE primary_table DROP referenced_column_name

The same rules apply if the "foreign table" is the "same table".

See also:

Upvotes: 2

Related Questions