无法在MySQL中删除外键 [英] cannot drop a foreign key in mySQL

问题描述

这是人与订单"之间的常见示例.我只是从Internet复制它作为测试.

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)
);

到现在，一切都成功了.但是如何删除外键PersonID?

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

我尝试过这个.

ALTER TABLE Orders
DROP FOREIGN KEY PersonID;

MySQL说:

1091-无法删除'PersonID';检查列/键是否存在

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

推荐答案

使用以下语句标识约束的名称:

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';

在ALTER TABLE语句中使用来自CONSTRAINT_NAME的结果.例如:

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

ALTER TABLE Orders
DROP FOREIGN KEY `myconstraint`;

从 查看全文