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).
Copyright (c) 2000, 2020, Oracle Corporation and/or its affiliates. All rights reserved.