CODEWITHSUNDEEP
mysql
GreedyAi
GreedyAi

Reputation: 2839

Can't Drop foreign key in MySQL

I have had a 1 to many relationship between course and instructor, which I wanted to drop. When I tried to drop the instructorID in course table it told me that. I couldn't drop it as it was a foreign key. Then I decided to drop it like this:

ALTER TABLE course DROP FOREIGN KEY instructorID

But i get this error : 

#1091 - Can't DROP 'InstructorID'; check that column/key exists

I don't get what this error means. what am i doing wrong?

Upvotes: 49

Views: 146965

Answers (7)

Bobby
Bobby

Reputation: 113

You need to delete the 'foreign key constraint' and the 'key'.

Alter Table <table name> drop foreign key <constraint_name> 
Alter table <table name> drop key <column name>

Upvotes: 7

jitendra
jitendra

Reputation: 1

Can't DROP 'string'; check that column/key exists: ALTER TABLE accreditor_architectures DROP string error show in terminal when remove column from data base in ruby on rails

Upvotes: 0

Dexter
Dexter

Reputation: 9344

If any of you still not able to DROP the table. Try this. You can able to see all the details by running this

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'ReferenceTableName'; <-- change only this

If you want to see just the constrains

SELECT
    CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'ReferenceTableName';

Upvotes: 3

PURVESH PATEL
PURVESH PATEL

Reputation: 209

To drop a FOREIGN KEY constraint:

MySQL:

ALTER TABLE Orders DROP FOREIGN KEY {Constraint/Key_name};

For SQL Server / Oracle / MS Access:

ALTER TABLE Orders DROP CONSTRAINT {Constraint/Key_name};

Upvotes: 2

JessicaRam
JessicaRam

Reputation: 21

the reason why you cannot drop InstructorID is because you need to use the name of the constraint of the Foreign key . KevDev specified that you must run 'SHOW CREATE TABLE course' to find the constraint name. after doing so , you can delete the foreign key. BUT wait theres more, the 'key' still stays behind which must get deleted. You can run 'SHOW CREATE TABLE' course to check that the key is still behind. once checking that it is still there then perform what Bobby advised. 'Alter table drop key' in doing so you have fully removed the the foreign key

Upvotes: 0

Elham Kohestani
Elham Kohestani

Reputation: 3162

After you run SHOW CREATE table course; you should find the fk symbol which is commonly like the one bellow:

(course_ibfk_1)

it may differ according to your mysql version you are using then drop the foreign key using the fk symbol as follow :

alter table course drop foreign key course_ibfk_1;

Upvotes: 46

kevdev
kevdev

Reputation: 1124

Please run an SHOW CREATE TABLE course; to make sure instructorID is the name of foreign key constraint.

Additional: The error means MySQL searches for a foreign key constraint named "InstructorID" but there is no constraint with such name, maybe this is your column name, but you have to use the constraint name to delete foreign keys.

Upvotes: 92

Related Questions