WL#11059: Implement INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as a system views over dictionary tables.
Affects: Server-8.0 — Status: Complete — Priority: Medium
This WL should implement new system view definition for INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS, reading metadata from data dictionary tables and avoid creation of temporary table for these I_S table as we do today for I_S.TABLES/COLUMNS/etc.
Abbreviation: I_S - INFORMATION_SCHEMA. DD - Data dictionary. SE - Storage engine. FR1: Implement I_S.REFERENTIAL_CONSTRAINTS system view over DD tables. Non-Functional Requirements: NFR1: User interface to I_S.REFERENTIAL_CONSTRAINTS should not change. NFR2: Related old I_S code should be removed.
A) Following are mapping of DD columns to system view definition of I_S.REFERENTIAL_CONSTRAINTS: - CONSTRAINT_CATALOG maps to mysql.catalogs.name. - CONSTRAINT_SCHEMA maps to mysql.schemata.name. - CONSTRAINT_NAME maps to mysql.foreign_keys.name. - UNIQUE_CONSTRAINT_CATALOG maps to mysql.foreign_keys.referenced_table_catalog. - UNIQUE_CONSTRAINT_SCHEMA maps to mysql.foreign_keys.referenced_table_schema. - UNIQUE_CONSTRAINT_NAME maps to mysql.foreign_keys.unique_constraint_name. - MATCH_OPTION maps to mysql.foreign_keys.match_option. - UPDATE_RULE maps to mysql.foreign_keys.update_rule. - DELETE_RULE maps to mysql.foreign_keys.delete_rule. - TABLE_NAME maps to mysql.tables.name. - REFERENCED_TABLE_NAME maps to mysql.foreign_keys.referenced_table_name. B) Dependency : The value of UNIQUE_CONSTRAINT_NAME can be calculated based on mysql.foreign_keys.unique_constraint_id. This ID does not point to required column right now and must be implemented. C) Performance: Implementing I_S tables as system views would make execution of these I_S tables faster when compared to 5.7. D) Upgrade: 5.7 to 8.0 Upgrade might not be affected. Database created before this WL, may not work with server that has this WL implemented. IMO, as we do not support DD to DD upgrade as of now, no action is required. E) Documentation: FR1 should be mentioned in documentation.
The I_S.REFERENTIAL_CONSTRAINTS system view definition: CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW information_schema.REFERENTIAL_CONSTRAINTS AS SELECT cat.name as CONSTRAINT_CATALOG, sch.name as CONSTRAINT_SCHEMA, fk.name as CONSTRAINT_NAME, fk.referenced_table_catalog as UNIQUE_CONSTRAINT_CATALOG, fk.referenced_table_schema as UNIQUE_CONSTRAINT_SCHEMA, fk.unique_constraint_name as UNIQUE_CONSTRAINT_NAME, fk.match_option as MATCH_OPTION, ", "fk.update_rule as UPDATE_RULE, ", "fk.delete_rule as DELETE_RULE, tbl.name as TABLE_NAME, fk.referenced_table_name as REFERENCED_TABLE_NAME FROM mysql.foreign_keys fk JOIN mysql.tables tbl ON fk.table_id = tbl.id JOIN mysql.schemata sch ON fk.schema_id= sch.id JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE CAN_ACCESS_TABLE(sch.name, tbl.name) AND IS_VISIBLE_DD_OBJECT(tbl.hidden);
Copyright (c) 2000, 2019, Oracle Corporation and/or its affiliates. All rights reserved.