WL#4175: Foreign Keys: Check tables

Affects: Server-7.x   —   Status: Un-Assigned

Sometimes foreign-key integrity will be violated, as a result
of operating with @@foreign_key_checks = 0, ALTER ... NOCHECK,
storage engine failure, etc. So MySQL should have a standard
way to check whether all is well.

The original suggestion in WL#148 was:
"
CHECK TABLE
-----------

It is not certain that "CHECK TABLE table_name FOREIGN KEYS"
will be the syntax that we use. This requires an HLS change.
Possibly we'd want "... CONSTRAINT constraint_name" instead.
But regardless of syntax, the "checking" works like this.
We check only for where table_name is referencing, not referenced.
It might be possible to do this with a stored procedure.

We assume that fk_prepare() will fail if .FRM is bad:
contains unparseable junk
or contains REFERENCED FROM nonexistent table
or contains REFERENCED FROM but referencing table doesn't contain REFERENCING
or contains REFERENCES nonexistent table
or contains REFERENCES but referenced table doesn't contain REFERENCED FROM
or contains duplicate clause.
If any of those scenarios are missed, add checks for them here.
Do not try to repair them, the only repair method is DROP TABLE.

check_table_foreign_keys()
  fk_prepare();
  for (each row in table)
    for (each constraint where table_name is a referencing table)


      fk_check_parent_list();
      if (error) write message to stdout
"

It is probably easier to write a stored procedure which finds what
a table's dependencies are, and selects where foreign_key_value
not in (referenced_table).