时间戳

首页 > 解决方案 > cannot drop a foreign key in mySQL

mysql - 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

标签： mysqlsqlforeign-keys

解决方案

使用以下语句识别约束的名称：

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

CONSTRAINT_NAME在你的ALTER TABLE陈述中使用结果。例如：

ALTER TABLE Orders
DROP FOREIGN KEY `myconstraint`;

答案来自MySQL 参考手册

推荐阅读