cannot drop a foreign key in mySQL

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

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

Advertisement

Answer