cannot drop a foreign key in mySQL

Question

It's a common example between Persons and Orders. I just copied it from Internet as a test.

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID) );

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(ID)
);

Till now it's all successful. But how can I drop the Foreign Key PersonID?

I tried this.

ALTER TABLE Orders
DROP FOREIGN KEY PersonID;

MySQL said:

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

The foreign key name you are trying to drop doesn't exist. You need to find it with a query. https://stackoverflow.com/questions/7765820/query-to-find-foreign-keys. Edit: if you don't name the constraint, it's usually named "table_name.column_name", so in your case, "Orders.PersonID". — T Gray, Nov 14 '18 at 21:18
"PersonID" is the name of the field on which the key applies, but is not (necessarily) the name of the key itself. As T Gray says, if you don't know the name of the key you'll have to query for it — ADyson, Nov 14 '18 at 21:20
Possible duplicate of [unable to drop the foreign key](https://stackoverflow.com/questions/10632587/unable-to-drop-the-foreign-key) — philipxy, Nov 14 '18 at 22:36
Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. — philipxy, Nov 14 '18 at 22:37

score 3 · Accepted Answer · answered Nov 14 '18 at 22:20

Identify the name of the constraint using the statement:

SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'Orders'
AND COLUMN_NAME = 'PersonID';

Use the result from CONSTRAINT_NAME in your ALTER TABLE statement. For example:

ALTER TABLE Orders
DROP FOREIGN KEY `myconstraint`;

Answer derived from the MySQL Reference Manual

score 0 · Answer 2 · edited Nov 14 '18 at 21:38

0

you need to delete constraint and also the key. First find the constraint by doing the following

1) SHOW CREATE table Orders;

result will be something like this

pick up value of CONSTRAINT.. in this screenshot 'fk_dayBreakUp_timetable'

2) ALTER TABLE Orders DROP foreign key << constraint_name >>

here you would do

3) ALTER TABLE Orders DROP key << column name >>

Hope this resolves your issue.

edited Nov 14 '18 at 21:38

KevinO

4,303
4
27
36

answered Nov 14 '18 at 21:34

Maneesh Parihar

1,395
2
8
6

This will work provided the foreign key was named. In his case, it was not, so the show create won't help. Without a name, the constraint is defaulted to "table.column". It's better to get the constraint as named from the dictionary, IMO. – T Gray Nov 14 '18 at 22:04

cannot drop a foreign key in mySQL

2 Answers2

Linked