Does MySQL always run foreign key constraint checks when a row is updated? Or only when a relevant column is changed?

user_3509128 0
Viewed 72

I have this question and I haven't found specific documentation to confirm the behavior and am unaware of how to manually check this myself.

Consider I have table A with b_id foreign key to table B. If I run an update on a row in table A, does mysql always run the foreign key constraint check on table B even if A's b_id goes unchanged or isn't passed in the update statement? such as (select 1 from B where id = ?)

Example:

UPDATE A set A.name = "x", A.b_id = 1 where A.id = 1 I know this runs the foreign key check on B

UPDATE A set A.name = "x" where A.id = 1 But does this also run the foreign key check even though b_id goes unchanged since it was not passed?

UPDATE A set A.name = "x" A.b_id = A.b_id where A.id = 1 And what about this? b_id gets passed in with same existing value. Does the fk check run?

Any supporting documentation or help would be appreciated, as well as tips on how I can test this sort of behavior myself since using EXPLAIN doesn't help.

Edit: this is for INNODB engine and mysql 8.0

sql-update constraints innodb foreign-keys mysql
2 Answers

Foreign key integrity is checked always when the server detects that the data is changed by fact and needs in save to the disk. It is checked after all BEFORE UPDATE triggers execution (rather than data type check which is performed each time, before and after each separate trigger).

The reason is simple. Server does not store any flag which marks does the value is changed - it is more expensive than direct compare before real, physical, UPDATE execution. The value changing is not tracked. Server does not know does the value to be saved during the update is provided by the query text or, for example, is provided by one of the BEFORE UPDATE triggers in the trigger chain.

small DEMO

user_101387340

When you add a foreign key constraint, MySQL requires an index:

MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created.

To clarify, this is an index in the referencing table (in your example A), not the referenced table (in your example B). The index in table B you have to provide yourself, otherwise you will get an error message.

Given this index, the answer to your question can be summarized to:

The foreign key constraint will be verified (e.g. the check if the row exists in the referenced table) whenever this index will be modified.

Since it may not be completely obvious when this happens, some examples: for a sample table of

CREATE TABLE A (id int primary key, b_id int, c int, 
    FOREIGN KEY (b_id) REFERENCES B(id))

the foreign key (e.g. if the value exists in the referenced table B) is checked for (assuming that such a row exists):

update A set id = 2 where id = 1

as it modifies the primary key, which is part of every index, so that hidden index gets modified, thus the verification will be executed, and

update A set b_id = 3 where b_id = 2

as it modifies the column b_id, which is part of the hidden index, so that hidden index gets modified.

It does NOT get checked for

update A set id = 2 where id = 2 
update A set b_id = 3 where b_id = 3
update A set b_id = b_id

as those do not modify the index content (because nothing changes).

Notably

update A set c = 2 where c = 3

also does not modify the index (as c is not part of the index), so the foreign key constraint is not verified even though the row itself changes.

To make things a bit more complicated, you can use your own index:

CREATE TABLE A (id int primary key, b_id int, c int, 
    INDEX b_c (b_id, c),
    FOREIGN KEY (b_id) REFERENCES B(id))

If you do that, MySQL can use this index for your foreign key and doesn't have to add its own hidden index. The effects of the updates mentioned above will not change, except for

update A set c = 2 where c = 3

as now, c is a column of the index that MySQL uses for the foreign key constraint, abd this update requires a modification of the index content - and thus triggers a verification. This is actually a verification that logically would not be required - it just happens because of how MySQL implemented it. So if you really want to prevent every unnecessary check, you could add an index on just column b_id yourself, then MySQL won't use the index on (b_id, c). But that would be extreme micro-optimization and you really should not do it just for this.

Please note that, while this may be an interesting technical detail to know, it should not have any impact on what you actually do with the database. You should not write your queries differently with that knowledge. If you need to update a row, you have to update the row. Also, adding an index just to prevent a check will most certainly not help. Also, since the behaviour is not specified, it can also change at any time (e.g. by you if you find and implement a better solution).

user_62485280
Related