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

Answer 1

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

1) SHOW CREATE table Orders;

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.

Answer 2

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