Foreign keys - temporarily bypass?

Question

Hi,

I have just started to learn about the pros of foreign keys in database design (mySQL / innoDB) and I wonder if there's any way to temporarily bypass the foreign key when doing a specific delete query, to just delete in the parent table, and not from the linked child tables.

Thanks

Answer 1

You can always issue a command to disable a particular key. Other than that there is no way that I know about to bypass a constraint. I'm guessing you are doing some batch load operations and need to disable the key for performance reasons?

Answer 2

Edit: the DISABLE KEYS doesn't bypass foreign key checks, only unique key checks.

To drop a foreign key constraint use:

ALTER TABLE <tablename> DROP FOREIGN KEY <constraintname>;

Then to restore the constraint, you need to re-declare the foreign key constraint in another ALTER TABLE statement.

See http://dev.mysql.com/doc/refman/5.1/en/alter-table.html for details.

But I agree with @HLGEM and I give his answer +1. Consider carefully if you really need to do this. Most tasks are in fact easier if you retain foreign key constraints.

You can declare your foreign keys with cascading effects to set child table values to NULL or a DEFAULT value so you can delete parent rows.

Answer 3

Do not even consider doing that, it will cause data integrity problems with your database. This is avery bad idea. The whole purpose of the foreign keys is to keep people from doing such a thing!

Answer 4

You can use a ON DELETE SET NULL clause on the foreign key. That will allow you to delete rows in the parent table. The rows in the child table referring to the deleted rows in the parent table will have the foreign key column set to NULL.

Personally I have never really needed that functionality.

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Answer 5

FOREIGN KEYs are supposed to keep the database consistent all the time. So it is against the very idea of foreign keys to have them in database and still let remove referenced rows…

Though… there is one case when temporarily breaking these constraints may make sense: during a transaction. Important thing is to have the database consistent before and after transaction and not to let any other database user see the inconsistent data, but during a transaction and only in the session doing the transaction, temporary inconsistency won't hurt. And SQL allows for this: you may define some foreign keys (and sometimes other constraints) deferrable and request deferring of enforcing them to the end of a transaction.

This way you may do a complicate set of changes on database, even removing some rows still references in other tables and still have the database consistent and the end of the transaction. Other database users won't even see the temporary inconsistency.

Update: It seems MySQL doesn't support deferred constraints. So this answer is probably not very useful.