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?
Asked
Active
Viewed 160 times
0
-
I'm not sure I understand what "a foreign key with a primary" even means...? – Joachim Isaksson Apr 11 '14 at 17:01
-
Uh yeah maybe I wrote it bad. I meant that I made the relation with the same table's primary key. – cyrfandli Apr 11 '14 at 17:02
1 Answers
2
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:
Community
- 1
- 1
user2864740
- 60,010
- 15
- 145
- 220
-
I don't really understand this. I've tried to drop the admin_id coloumn but that gave the same message. And the PK is in the same table the connection is with. – cyrfandli Apr 11 '14 at 17:06
-
@cyrfandli No column is dropped or altered when dropping the FK *constraint*. As such, the above violation will not occur. – user2864740 Apr 11 '14 at 17:13