mysql - 删除级联时外键约束无效

Question

我创建了两个表(emp 和 dept)。 Emp 包含外键 (deptid)。我试图从部门表中删除一行，但收到​​外键约束错误。然后我更改了 emp 表外键以添加约束并在级联上删除。代码复制到这里

create table dept (
  deptid int primary key,
  deptname varchar(20),
  locid int);

create table emp (
  empid int primary key,
  frname varchar(15),
  lname varchar(20),
  hiredate datetime,
  deptid int,
  foreign key (deptid) references dept(deptid));

alter table emp add constraint fk_deptid foreign key (deptid) 
    references dept(deptid) on delete cascade;

这是我收到的错误消息:

Query Error: Error: ER_ROW_IS_REFERENCED_2: Cannot delete or update a parent row: a foreign key constraint fails (test.emp, CONSTRAINT emp_ibfk_1 FOREIGN KEY (deptid) REFERENCES dept (deptid))

Answer 1

问题是您正在为同一列创建两个外键约束。避免这样做，这会令人困惑。

• 第一个是在您创建表时创建的，并且未命名。由于您没有为其提供名称，MySQL 会自动将其命名为 emp_ibfk_1。这个没有具有CASCADE DELETE并且可以防止删除父 key 。

• 第二个位于单独的 SQL 语句上，您将其命名为 fk_deptid。这个具有CASCADE DELETE并允​​许删除父 key 。

在强制执行外键约束时，MySQL 无法删除该行，因为第一个约束不允许这样做。这就是您遇到的错误。

这是一个令人困惑的情况，我会避免做这样的事情。我建议对该列使用单个 FK 约束；具体来说，我会删除第一个。

例如:

create table dept (
  deptid int primary key,
  deptname varchar(20),
  locid int);

create table emp (
  empid int primary key,
  frname varchar(15),
  lname varchar(20),
  hiredate datetime,
  deptid int
  -- , foreign key (deptid) references dept(deptid) -- removed
);

alter table emp add constraint fk_deptid foreign key (deptid) 
    references dept(deptid) on delete cascade;

insert into dept (deptid, deptname, locid) 
  values (1, 'Math', 123);    

insert into emp (empid, frname, lname, hiredate, deptid) 
  values (10, 'Peter', 'Fonda', null, 1);

delete from dept where deptid = 1; -- works!