Multi statement SQL delete with foreign key constraint

Question

I'm using MySQL 5.5 with a complex schema that has foreign key constraints.

I want to execute multiple update and delete SQL statements.

After a subset of these statements have executed there may be foreign key constraint violations.

After all of these statements have executed there shouldn't be foreign key constraint violations.

I know about cascade, I don't want to use it because some of the changes require more complex logic.

My question is, is there a way to combine my multiple statements together (as a multi statement, transaction, etc) such that the foreign key constraints are not checked until after they are all completed? (And if there is an error than none of them are executed?)

Andomar · Accepted Answer · 2012-12-16 09:33:57Z

MySQL does not implement deferred constraint checking. From the manual,

Deviation from SQL standards: Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things will be impossible, such as deleting a record that refers to itself using a foreign key.

So there is no way to check constraints only at the end of a group of SQL statements.

Collectives™ on Stack Overflow

Multi statement SQL delete with foreign key constraint

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related