Can Referential Integrity Checks Be Deferred Until Commit in MySQL?

Can Referential Integrity Checks Be Deferred Until Commit in MySQL?

In MySQL, referential integrity checks are performed immediately, not deferred until transaction commit. This limitation, as highlighted in the MySQL documentation, prevents certain operations, such as deleting a record that refers to itself using a foreign key.

The question of deferring referential integrity checks has been raised before, as seen in the referenced PoEAA question. The problem arises when inserting data into multiple related tables within a single transaction. Constraints errors can occur when attempting to insert into join tables due to immediate referential integrity checking.

According to the SQL standard, constraint checking should be deferred. However, InnoDB, the default storage engine in MySQL, currently enforces it row-by-row during an SQL statement. This means that it sets shared row-level locks on referenced or parent records, preventing the transaction from proceeding.

Therefore, in MySQL with InnoDB as the storage engine, deferring referential integrity checks until commit is not currently possible. This limitation poses challenges for certain database operations and requires careful consideration when designing the database schema.

The above is the detailed content of Can Referential Integrity Checks Be Deferred Until Commit in MySQL?. For more information, please follow other related articles on the PHP Chinese website!