mysql Delete and Database Relationships

Question

If I'm trying to delete multiple rows from a table and one of those rows can't be deleted because of a database relationship, what will happen?

Will the rows that aren't constrained by a relationship still be deleted? Or will the entire delete fail?

Thanks, Colin

Answer 1

In MySQL, if you set a foreign key constraint, the query will fail if you try to insert a non-existing ID, or try to delete an existing ID.

In other words, your delete will fail.

Answer 2

If it's a single delete statement, then the entire delete will fail.

Answer 3

All the rows will delete just fine. However, you should make sure that your program deletes related rows otherwise missing posts/records/whatever may ensue.

Answer 4

There is a more general question here:

• If I do an SQL statement which affects multiple rows, and it encounters an error after modifying some rows, what happens.

The answer is essentially "None of them are affected, even ones which had already succeeded".

What happens internally is rather complicated. InnoDB supports transaction savepoints, and the database creates an implicit savepoint at the beginning of the statement within the current transaction. If the statement fails part way through, a rollback happens back to the implicit savepoint. This means that it then looks like the statement never happened (except if people insist in using READ_UNCOMMITTED isolation level, which they should not if they care).

This happens whether you're using explicit transactions or not. If you are using explicit transactions, the current transaction is not rolled back (except on certain types of error such as deadlock and lock wait timeout, where it must do to allow a deadlock to be broken), instead it only rolls back as far as the beginning of the statement.