I guess I’m first to post in 2012 so Happy New Year all blog readers !
Now back to HardCore MySQL business – foreign Keys. MySQL supported Foreign Keys for Innodb for many years, yet rudimentary support initially added in MySQL 3.23.44 have not been improved in new releases as much as I’d like. We still get cryptic error messages such as “ERROR 1025 (HY000): Error on rename of ‘./test/child’ to ‘./test/#sql2-496-40a5’ (errno: 152)” in many cases and foreign keys are still handled on storage engine level making them not working for partitioned tables as well as making foreign keys performed row by row which often can be very inefficient.
As results of Foreign Key limitations you might need to get rid of them, yet this leaves you up for a final bite – dropping foreign keys requires table rebuild. Yes you get it right even though Innodb is able to drop indexes without rebuilding table since MySQL 5.1 (Innodb Plugin) dropping foreign keys which should only require meta data change needs a table rebuild.

In some real life production cases you might find tables which are large enough it will range from inconvenient to not acceptable. As result you might have to resort to dirty solutions. Here is a real example. Assume you have parent and child tables and you want to archive some records from parent table which will make some children orphans. Right solution of course would b to update the children table first but it might be too expensive. MySQL Allows you for plenty of dirty tricks, and you can actually disable foreign keys to make it happen:

CREATE TABLE parent (
parent_id int(11) NOT NULL,
v varchar(10) DEFAULT NULL,
PRIMARY KEY (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE child (
parent_id int(11) NOT NULL,
v varchar(10) DEFAULT NULL,
KEY parent_id (parent_id),
CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

INSERT INTO parent(parent_id) VALUES(1,””);
INSERT INTO child(parent_id) VALUES(1,””);

set foreign_key_checks=0;
delete from parent;

After these actions we have child table which points to non existing row in parent table. Such table however will behave a little bit funny when you will try to update data stored in it. If you do not change “parent_id” column you can update data just fine, however if you want to change the parent_id column, even if you change it to the same value it will not work. This can cause problems to Hibernate and other ORM based applications which often like to update all columns whenever they have changed or not:

mysql> set foreign_key_checks=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from child;
+———–+——+
| parent_id | v |
+———–+——+
| 1 | NULL |
+———–+——+
1 row in set (0.00 sec)

mysql> update child set v=”aaa”;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> update child set parent_id=1 and v=’BBB’;
ERROR 1452 (23000): Cannot add or update a child row: 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_ley_checks=0) will update references to point to new table, while you can drop

Moreover Innodb is very protective when it comes to foreign constraints and DDL operations. Even if you have foreign keys disabled renaming parent table will have reference in child table updated to point to new table. However you can drop parent table without any errors, yet this will leave child table reference in fact and if you create the table with same name again it will need to be able to satisfy foreign key conditions (have column child table is referencing and have it of proper type and indexed):

mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table parent rename parentx;
Query OK, 0 rows affected (0.05 sec)

mysql> drop table parentx;
Query OK, 0 rows affected (0.61 sec)

mysql> show create table child \G
*************************** 1. row ***************************
Table: child
Create Table: CREATE TABLE child (
parent_id int(11) NOT NULL,
v varchar(10) DEFAULT NULL,
KEY parent_id (parent_id),
CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parentx (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> create table parentx(i int) engine=innodb;
ERROR 1005 (HY000): Can’t create table ‘test.parentx’ (errno: 150)

Innodb will enforce foreign key constrains to non existing table the same way as when there are non existing rows – if you’re trying to change “parent_id” column in child table, even to same value you will get foreign key violation error.

So Innodb Foreign Keys are pretty good at keeping you hostage.

Do you have any better idea how to Hijack innodb Foreign Keys or drop them without rebuilding the table ?

6 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Danil Zburivsky

I think in most cases you should be able to drop foreign key without rebuilding whole table by using any online schema changes approaches available out there: create a new table with no FK, copy data, synchronize, rename.
Though, having orphaned rows in child table could cause issues in application logic, so I’d better fix it but putting missing links back.
But I agree, having the ability to instantly change FK definition after RENAME TABLE and not being able to drop it without table rebuild freaks me out 🙂

0
Peter Zaitsev

Thanks Danil,

Though I fail to see how “online schema change” avoids to rebuild the table – it just does it online. Though work still needs to be done, right ?

0
John LaRocque

Came from an Oracle background and just trying to confirm what you’re saying…

Are you saying that whenever I attempt to modify an InnoDB table’s foreign keys (drop / recreate) I really need to rebuild the table? Right now my tables are large in number, but not in terms of content. I’m encountering the issue with creating the table you described above which unfortunately is a central table to my data model.

0
Anonymous

Another option would be to backup the current database, record and “replay” queries executed from that point, and then bring the new database online. When switching to a new database + inserting the query, this whole process could take less than a few seconds.
Otherwise, even a 5 minute offline message is most times acceptable? Because importing the modified tables when you do the hard work beforehand, is not that much work.

Of course this will require some hardcore coding in most cases, but I see a possibility here.

0
Aziz DURMAZ

First create parent_copy table with parent datas. When its finish, enter “set foreign_key_checks=0;” to stop checks. And drop parent table. Dropping table is different than rename table. It does not change definition of foreign key.

Then “RENAME TABLE parent_copy to parent “. And “set foreign_key_checks=1;” to set everything to normal.

FK is working perfectly…

0
Tim Birkett

To expand on what Aziz said above. Here’s what I do. Let’s say I need to do some action on a large table like a big DELETE from and it takes FOREVER.

CREATE TABLE parent_new LIKE parent;
ALTER TABLE parent_new DISABLE KEYS;
INSERT INTO parent_new (parent_id) VALUES (1, “”); # Or result of a SELECT
RENAME TABLE parent TO parent_old, parent_new TO parent;
ALTER TABLE parent ENABLE KEYS;

# FK points to parent_old right now…

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE parent_old;
RENAME TABLE parent TO parent_old, parent_old TO parent;
SET FOREIGN_KEY_CHECKS=1;

# An Atomic rename of the parent table to parent_old and back again will update all the FK references to point to parent.

0