mysql引用完整性_在MySQL中，我可以將引用完整性檢查推遲到提交

As in this question, I've been reading PoEAA and wondering if it's possible to defer referential integrity checks until commit in MySQL.

在這個問題中，我一直在閱讀PoEAA，並想知道是否可以將引用完整性檢查推遲到在MySQL中提交。

I've run into this problem when wanting to insert a bunch of products and related products in the same commit. Even within a transaction, I get constraint errors when I try to insert into the related_products join table.

當我想在同一個提交中插入一系列產品和相關產品時，我遇到了這個問題。即使在事務中，當我試圖插入到related_products連接表時，也會出現約束錯誤。

If it helps, I'm using PHP PDO for database connections.

如果有幫助，我將使用PHP PDO進行數據庫連接。

I'd appreciate any help you could offer.

如果你能提供任何幫助，我將不勝感激。

3 个解决方案

#1

52

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.

與一般的MySQL類似，在插入、刪除或更新許多行的SQL語句中，InnoDB逐行檢查唯一和外鍵約束。在執行外鍵檢查時，InnoDB將在子或父記錄上設置共享的行級鎖。InnoDB立即檢查外鍵約束;檢查不會延遲到事務提交。根據SQL標准，默認行為應該延遲檢查。也就是說，只有在處理完整個SQL語句之后才檢查約束。在InnoDB實現延遲約束檢查之前，有些事情是不可能的，比如刪除使用外鍵引用自己的記錄。

Back to the drawing board.

回到繪圖板上。

#2

14

If you are asking if MySQL supports the DEFERRABLE attribute for foreign keys (including the option INITIALLY DEFERRED) then the answer is a clear no.

如果您問MySQL是否支持外鍵的可延遲屬性(包括最初延遲的選項)，那么答案顯然是否定的。

You can't defer constraint checking until commit time in MySQL.

在MySQL中提交時間之前，不能延遲約束檢查。

And - as you have already pointed out - they are always evaluated at "row level" not on "statement level".

而且——正如您已經指出的那樣——它們總是在“行級”而不是“語句級”進行評估。

#3

9

You may handle this limitation of innodb engine, by temporarily disabling foreign key checks by setting server variable:

您可以通過設置服務器變量暫時禁用外鍵檢查來處理innodb引擎的這個限制:

set foreign_key_checks=0;

From MySQL manual:

從MySQL手冊:

mysqldump also produces correct definitions of tables in the dump file, and does not forget about the foreign keys.

mysqldump還生成轉儲文件中表的正確定義，並且不會忘記外鍵。

To make it easier to reload dump files for tables that have foreign key relationships, mysqldump automatically includes a statement in the dump output to set foreign_key_checks to 0. This avoids problems with tables having to be reloaded in a particular order when the dump is reloaded. It is also possible to set this variable manually:

為了更容易地重新加載具有外鍵關系的表的轉儲文件，mysqldump會自動在轉儲輸出中包含一條語句，將foreign_key_check設置為0。這避免了在重新加載轉儲時必須按特定順序重新加載表的問題。也可以手動設置該變量:

mysql> SET foreign_key_checks = 0;

mysql> SOURCE dump_file_name;

mysql> SET foreign_key_checks = 1;