Description:
Alter ignore should also apply to foreign key constraints. It is only documented
for unique contraints.
Manual :
IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled.
see : http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
The IGNORE keyword would have the effect of deleting the rows that do not comply to the constraint :
delete from myTable t where not exists(select null from referencedTable r where r.key = t.key);
ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...
The ignore would apply in this case :
alter_specification:
...
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
How to repeat:
drop database if exists alter_ignore;
create database alter_ignore;
use alter_ignore;
create table parent(id int primary key) engine=InnoDB;
insert into parent(id) values (1);
create table child(id int, parent_id int, key parent_fk(parent_id)) engine=InnoDB;
insert into child(id,parent_id) values (1,1);
insert into child(id,parent_id) values (2,2);
alter table child add constraint parent_fk foreign key (parent_id) references parent(id);
alter ignore table child add constraint parent_fk foreign key (parent_id) references parent(id);
delete from child where parent_id = 2;
alter table child add constraint parent_fk foreign key (parent_id) references parent(id);
Script execution :
mysql>
mysql> drop database if exists alter_ignore;
Query OK, 2 rows affected (0.06 sec)
mysql> create database alter_ignore;
Query OK, 1 row affected (0.01 sec)
mysql> use alter_ignore;
Database changed
mysql>
mysql> create table parent(id int primary key) engine=InnoDB;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into parent(id) values (1);
Query OK, 1 row affected (0.00 sec)
mysql> create table child(id int, parent_id int, key parent_fk(parent_id)) engin
e=InnoDB;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into child(id,parent_id) values (1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into child(id,parent_id) values (2,2);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> alter table child add constraint parent_fk foreign key (parent_id) refere
nces parent(id);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`alter_ignore`.<result 2 when explaining filename '#sql-2820_9'>, CONSTRAI
NT `parent_fk` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
mysql>
mysql> alter ignore table child add constraint parent_fk foreign key (parent_id)
references parent(id);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`alter_ignore`.<result 2 when explaining filename '#sql-2820_9'>, CONSTRAI NT `parent_fk` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
mysql>
--- these 2 statements below should be the behavior of the alter ignore table above :
mysql> delete from child where parent_id = 2;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> alter table child add constraint parent_fk foreign key (parent_id) refere
nces parent(id);
Query OK, 1 row affected (0.12 sec)
Records: 1 Duplicates: 0 Warnings: 0
