Have Geek Brain

Mysql Delete and Database Relationships

If I am trying to delete multiple rows from a table and one of those rows cannot be deleted due to a database relationship, what happens?

Will the lines be deleted that are not limited by the link? Or will the complete delete be removed?

+2


a source to share


4 answers


If it is a single delete statement, then a complete delete will fail.



+1


a source


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



In other words, your deletion will fail.

+2


a source


All lines will only be removed with a penalty. However, you have to make sure that your program is deleting related lines, otherwise there are no messages / entries / whatever might happen.

0


a source


There is a more general question here:

  • If I execute an SQL statement that touches multiple rows and it encounters an error after changing some rows, that's what happens.

The answer is essentially "none of them are affected, not even those that have already succeeded."

What's going on inside is pretty complicated. InnoDB supports transactional savepoints, and the database creates an implicit savepoint at the beginning of a statement in the current transaction. If the assertion fails partially, it rolls back to the implicit savepoint. This means it then looks like there never was such a statement (except when people insist on using the READ_UNCOMMITTED isolation level, which they shouldn't do if they're interested).

This happens regardless of whether you are using explicit transactions or not. If you are using explicit transactions, the current transaction is not rolled back (except for certain types of errors, such as deadlocks and a wait lock timeout where necessary so that a deadlock can be blocked), it only rolls back the beginning of the assertion instead.

0


a source






More articles:


All Articles