mysql - Cannot add or update a child row: a foreign key constraint fails -


i've table

1.

create table `parent` ( `id` bigint(19) not null default '0',  primary key (`id`), ) engine=innodb default charset=utf8; 

2.

create table `child_1` ( `parentid` bigint(19) not null default '0', `id` bigint(19) not null default '0', primary key (`parentid`,`id`), key `child_1_fk1_idx` (`parentid`,`id`), constraint `child_1_fk1` foreign key (`parentid`, `id`) references `child_2` (`parentid`,   `id`) on delete cascade ) engine=innodb default charset=utf8; 

3.

create table `child_2` ( `parentid` bigint(19) not null default '0', `id` bigint(19) not null default '0', primary key (`parentid`,`id`), key `child_2_fk1_idx` (`parentid`), constraint `child_2_fk1` foreign key (`parentid`) references `parent` (`parentid`) on delete cascade ) engine=innodb default charset=utf8; 

unfortunately, i've deleted second table constraint.

constraint `child_1_fk1` foreign key (`parentid`, `id`) references `child_2` (`parentid`,   `id`) on delete cascade 

how can recover that?

try (not tested):

alter table child_1 add foreign key (`parentid`, `id`)  references `child_2` (`parentid`,   `id`)  on delete cascade 

update:

to records violating constraint, run this:

select child1.id     child_1 child1 left join child_2 child2 on child1.parentid=child2.parentid ,  child1.id=child2.id child2.id null , child2.parentid null; 

Comments

Popular posts from this blog

Detect support for Shoutcast ICY MP3 without navigator.userAgent in Firefox? -

web - SVG not rendering properly in Firefox -

java - JavaFX 2 slider labelFormatter not being used -