I think that the same code should be useful for implementing the tricky part of MDEV-16356: validating the to-be-created constraints and then just adding the constraint without rebuilding the child table, like we currently do (when foreign_key_checks are enabled).

I think both should be the default. However we should add options for CHECK TABLE to allow user to specify
NO FOREIGN KEY CHECKS, NO CHILD CHECK, NO PARENT CHECK.

I think doing this with a select will be 100x slower compared to do it with explicit code.
A merge on ordered key is something the optimizer is not good at doing.
It is also much easier to give a complete list of errors with explicit code.

With explicit code we can also easier implement a FAST check, where we do a checksum for all unique reference keys in the child and check that the checksum for the parent over all keys are the same.

• how "foreign keys not being up to date" can lead to crashes? this looks like a bug
• NO FOREIGN KEY CHECKS, NO CHILD CHECK, NO PARENT CHECK, etc looks too complex, is there anyone actually requesting it? If not, then
  

  SET STATEMENT foreign_key_checks=0 CHECK TABLE

  can be used to disable foreign key checks. This can be later extended to
  

  SET STATEMENT check_constraint_checks=0 CHECK TABLE

  etc

monty,

With explicit code we can also easier implement a FAST check, where we do a checksum for all unique reference keys in the child and check that the checksum for the parent over all keys are the same.

It seems to me that we can ensure the integrity with a checksum only for one-to-one relationships (bijections). Normal foreign key relation doesn't have this property.

The implementation is merely straightforward: both for parent and child relations, check that for each record in child table, there is a record in parent table, that matches a given foreign key.

I had to modify the prelocking to lock both parents and children.
Also I made some refactoring around FOREIGN_KEY_INFO: now its fields are correct Lex_cstring-derived types, and are stored by value, not by pointer.

sanja, please review the following commits on branch bb-11.7-check-table:

2dea7185 MDEV-34309 [2/2] CHECK TABLE: implement referential integrity check

ac27db6f MDEV-34309 [1/2] prelock referentially related tables for CHECK TABLE

ee5cf521 FOREIGN_KEY_INFO: Store Lex_* strings without extra pointer indirection.

6c1a01d9 Fix deallocation of Sql_cmd_dml::result

I rebased it on main to bb-11.8-check-table.

I also see 8 commits there who review other 4 ?

Sergei reviews them.

It is OK to push after renaming commits related to this MDEV starting with: MDEV-34309, i.e something like this.:

MDEV-34309 prereq. FOREIGN_KEY_INFO: Store Lex_* strings without extra pointer indirection.

MDEV-34309 prereq. Fix deallocation of Sql_cmd_dml::result

Just to link them to the task which inspeared you for such changes (I'd put the MDEV-34309 before your cleanup also)

cleanup is ok too

It's been agreed that the feature won't go to 11.8 as we try to find legacy issues when

• foreign keys are claimed to be broken when they should be okay (example: MDEV-35962),
• foreign keys pretend to work when they probably shouldn't (example: MDEV-35908, INET6 column referencing TIME column),
• there is no clarity whether the foreign keys should work or not (example: MDEV-35936 – different field lengths, more in the comments to MDEV-35908) – this may be just a documentation issue, but it would be really good to get it documented while we are at it.

The goal is to avoid the hard choice between releasing CHECK which alerts about fake problems, or CHECK which doesn't show real problems, or CHECK which is inconsistent with DML/DDL related to the same foreign keys.

Thus, while the bugs will probably be fixed outside the scope of this task, as the fixes would go to earlier versions when possible, the testing scope of the task will increase.