In MySQL, can I defer referential integrity checks until commit

by

Looks like my answer is here…

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.

Back to the drawing board.

More Related Contents:

  1. MySQL foreign key to allow NULL?
  2. How to use IN & NOT IN same query [closed]
  3. MySQL: Large VARCHAR vs. TEXT?
  4. MySQL: is a SELECT statement case sensitive?
  5. Check overlap of date ranges in MySQL
  6. 1114 (HY000): The table is full
  7. UPDATE multiple tables in MySQL using LEFT JOIN
  8. How to raise an error within a MySQL function
  9. Multiple-column foreign key in MySQL?
  10. What is maximum query size for mysql?
  11. Finding all parents in mysql table with single query (Recursive Query)
  12. Get the latest date from grouped MySQL data
  13. How to execute a MySQL command from a shell script?
  14. Select most common value from a field in MySQL
  15. MySQL update a joined table
  16. Returning the ‘last’ row of each ‘group by’ in MySQL
  17. Optimal MySQL settings for queries that deliver large amounts of data?
  18. mysql configuration stops at “starting server”
  19. Is it possible to reference one column as multiple foreign keys?
  20. MySql Query: Select top 3 rows from table for each category
  21. how to use DISTINCT ON with mysql using ActiveRecord
  22. Split a string and loop through values in MySQL stored procedure
  23. Create an index on a huge MySQL production table without table locking
  24. MySQL concurrency, how does it work and do I need to handle it in my application
  25. mysql select id and name from other table and join query
  26. How to import a SQL Server .bak file into MySQL?
  27. MySQL: Insert datetime into other datetime field
  28. Import CSV to Update rows in table
  29. Invoke pusher when mysql has changed [closed]
  30. Summing a comma separated column in MySQL 4 (not 5)

Leave a Comment