In MySQL, can I defer referential integrity checks until commit

mysql referential-integrity
Ross McFarlane picture Ross McFarlane · Feb 16, 2011 · Viewed 16.8k times · Source

As in this question, I've been reading PoEAA and wondering if it's possible to defer referential integrity checks until commit in 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.

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

I'd appreciate any help you could offer.

Answer

Ross McFarlane picture Ross McFarlane · Feb 16, 2011

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.

Related questions

MySQL foreign key to allow NULL?

I'm piecing together an image website. The basic schema's pretty simple MySQL, but I'm having some trouble trying to represent possible admin flags associated with an image ("inappropriate", "copyrighted", etc.). My current notion is as follows: tblImages ( imageID INT UNSIGNED …

Read More
mysql Multiple Foreign Keys in a Table to the Same Primary Key

I have a table user with userID as the primary key. I have another table called Friends. In the Friends table, I have two Users as friends represented by the columns UserID and FrndID where both UserID and FrndID should …

Read More
mysql circular dependency in foreign key constraints

Given the schema: What I need is having every user_identities.belongs_to reference an users.id. At the same time, every users has a primary_identity as shown in the picture. However when I try to add this reference …

Read More