… a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`parent_id`)) Renaming parent table (even with foreign_…indexed): mysql> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql> alter table parent rename parentx; …
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
… KEY | empid | NULL | NULL | +————–+————+—————–+—————–+————-+———————–+————————+ alter table emp drop index fk_deptid;ERROR 1553 (HY000): Cannot drop index ‘fk_deptid’: needed in a foreign key constraint +++Query…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… avoid dropping keys that are part of a FOREIGN KEY constraint; mysqldump –innodb-optimize-keys ignores foreign keys because InnoDB requires a full table rebuild on foreign key changes. So adding them back with a separate ALTER TABLE…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…Foreign key constraint fails for table `test/child`: , CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE Trying to add in child table…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 4 Comments
… table scan is performed) Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> ALTER TABLE test_table… on to a more interesting scenario: foreign keys mysql> CREATE TABLE parent (id INT NOT NULL, -> PRIMARY KEY (id) -> ) ENGINE=INNODB; — 0 Tickets Used… to most users? INSERT w/PRIMARY KEY defined: Number of rows inserted – 1 INSERT w/FOREIGN KEY constraint: Number of rows inserted – 1 SELECT…
Comment: How to recover a single InnoDB table from a Full Backup
January 31, 2012 By 7 Comments
Good point 
ALTER TABLE customer DISCARD TABLESPACE; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails () Added to the blog post, thank you.
Comment: Innodb locking and Foreign Keys
August 4, 2008 By 2 Comments
Do You know sth. about altering table with drop foreign key with constraint ?? Because i got only errors. Querys: ALTER TABLE `lpq` drop index `id_lpc`; ALTER TABLE `lpq` DROP FOREIGN KEY id_lpc ; ALTER TABLE `lpq` DROP Index id_lpc ; ALTER TABLE `lpq` DISABLE KEYS And so on. Only Error 150 or 1005
