Bug #46281 report foreign key violations before doing the entire alter
Submitted: 18 Jul 2009 9:02
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:5.0, 5.1, 5.4 OS:Any
Assigned to: Geir H√łydalsvik CPU Architecture:Any

[18 Jul 2009 9:02] Shane Bester
Description:
mysql first does the alter table, then throws the error about foreign key violations.  some time could be saved for huge tables if the error occurred immediately, instead of after the data is copied.

in the 'how to repeat', if the parent table is many gigabytes in size, it would be great to see the error immediately, instead of after wasted minutes or hours.

How to repeat:

drop table if exists child,parent;
create table parent (id int not null,primary key (id)) engine=innodb;
set @a=1;
insert into parent values (0);
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
insert into parent select @a:=@a+1 from parent;
create table child (id int, parent_id int,index par_ind 
(parent_id),foreign key (parent_id) 
references parent(id) on delete cascade) engine=innodb;
alter table parent drop primary key;

#the feature request is the ALTER TABLE should give an error immediately.
[18 Jan 2010 17:25] Valeriy Kravchuk
Bug #50384 was marked as a duplicate of this one, although it describes a bit different case of dropping non-existent foreign key. I also think this is a bug, not just a feature request.