mysql error 1451

Question

I have mysql error with code 1451.

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

here sql statement :


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

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

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

Thank You.

Kshitiz Kshitiz · Accepted Answer · 2013-04-15T09:48:12

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;

mysql error 1451

2 Answers