mysql 错误 1451

mysql error 1451

提问by crazymob

I have mysql error with code 1451.

我有代码 1451 的 mysql 错误。

Cannot delete or update a parent row: a foreign key constraint fails (online_store_admin.osa_admin_logs, CONSTRAINT fk_admins_logsFOREIGN KEY (aid) REFERENCES osa_admins(aid))

无法删除或更新父行：外键约束失败 ( online_store_admin. osa_admin_logs, CONSTRAINT fk_admins_logsFOREIGN KEY ( aid) REFERENCES osa_admins( aid))

here sql statement :

这里的 sql 语句：

drop table if exists osa_admins;
create table if not exists osa_admins(
 aid int unsigned not null auto_increment, 
 uid varchar(50) not null, 
 pass char(41) not null, 
 erp_id int unsigned not null, 
 last_login int unsigned not null,
 is_block tinyint unsigned not null,
 menus varchar(50) not null,
 is_login tinyint unsigned not null,
 ip_login char(15) not null,

 constraint idx_osa_admins primary key using btree(aid)
);
insert into osa_admins value
(NULL, 'root', password('6789'), '0', '0', '0', '*', '0', '127.000.000.001'),
(NULL, 'ryu', password('6789'), '0', '0', '0', '*', '0', '127.000.000.001');

drop table if exists osa_admin_logs;
create table if not exists osa_admin_logs(
 lid bigint unsigned not null,
 aid int unsigned not null,
 dates int unsigned not null,
 logs text not null,

 constraint idx_osa_admin_logs primary key using btree(lid),
 constraint fk_admins_logs foreign key (aid)
  references osa_admins(aid)
  match full
  on update cascade
  on delete cascade
);
insert into osa_admin_logs values
(NULL, '2', '0', 'some action here'),
(NULL, '2', '0', 'again, some action here');

drop table if exists osa_admins;
create table if not exists osa_admins(
 aid int unsigned not null auto_increment, 
 uid varchar(50) not null, 
 pass char(41) not null, 
 erp_id int unsigned not null, 
 last_login int unsigned not null,
 is_block tinyint unsigned not null,
 menus varchar(50) not null,
 is_login tinyint unsigned not null,
 ip_login char(15) not null,

 constraint idx_osa_admins primary key using btree(aid)
);
insert into osa_admins value
(NULL, 'root', password('6789'), '0', '0', '0', '*', '0', '127.000.000.001'),
(NULL, 'ryu', password('6789'), '0', '0', '0', '*', '0', '127.000.000.001');

drop table if exists osa_admin_logs;
create table if not exists osa_admin_logs(
 lid bigint unsigned not null,
 aid int unsigned not null,
 dates int unsigned not null,
 logs text not null,

 constraint idx_osa_admin_logs primary key using btree(lid),
 constraint fk_admins_logs foreign key (aid)
  references osa_admins(aid)
  match full
  on update cascade
  on delete cascade
);
insert into osa_admin_logs values
(NULL, '2', '0', 'some action here'),
(NULL, '2', '0', 'again, some action here');

Problem come when i use this statement:

当我使用这个语句时出现问题：

delete from osa_admins where aid='2';

从 osa_admins 中删除，其中 aid='2';

i think i had set "on delete cascade". anyone know how to delete cascade? so i don't necessary to manual detelet osa_admin_logs data. oh, i using innodb as db engine(default mysql that i have).

我想我已经设置了“删除级联”。有人知道如何删除级联吗？所以我不需要手动删除 osa_admin_logs 数据。哦，我使用 innodb 作为数据库引擎（我拥有的默认 mysql）。

And sorry i ask same question that had answer, just let me know where i can get my question.

对不起，我问了同样的问题，只要让我知道我在哪里可以得到我的问题。

Thank You.

谢谢你。

回答by Kshitiz

Use the following commands to do this:

使用以下命令执行此操作：

SET foreign_key_checks = 0; 
DELETE FROM your_table_name WHERE your_condition;
SET foreign_key_checks = 1;

回答by The Scrum Meister

remove the match fullfrom the constraint

match full从约束中删除

Use of an explicit MATCH clause will not have the specified effect, and also causes ON DELETE and ON UPDATE clauses to be ignored. For these reasons, specifying MATCH should be avoided.

使用显式 MATCH 子句不会产生指定的效果，还会导致 ON DELETE 和 ON UPDATE 子句被忽略。由于这些原因，应避免指定 MATCH。

MySql docs http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

MySql 文档http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html