The dependencies between database objects are the dependency and reference relationships between the database objects. This topic describes how to create and query the dependencies between database objects in OceanBase Database in MySQL mode.
Some types of schema objects can reference other objects in their definitions. For example, the definition of a view may be a query that references a table or another view. If the definition of Object A references Object B, Object A is the dependent object of Object B, and Object B is the referenced object of Object A.
The dependencies of views on views and views on tables exist in a MySQL tenant. You can query these dependencies by using the VIEW_TABLE_USAGE view in the information_schema database.
Example 1: Create a view named view2 that references the tbl1 table and the view1 view.
obclient> CREATE TABLE tbl1(col1 INT, col2 INT);
Query OK, 0 rows affected
obclient> CREATE TABLE tbl2 (col1 INT, col2 INT);
Query OK, 0 rows affected
obclient> CREATE VIEW view1 AS SELECT * FROM tbl2;
Query OK, 0 rows affected
obclient> CREATE VIEW view2 AS SELECT t.col1 AS col1, t.col2 AS col2 FROM tbl1 t, view1 v WHERE
t.col1 = v.col2;
Query OK, 0 rows affected
Example 2: Query the dependencies between database objects by using the VIEW_TABLE_USAGE view of the information_schema database.
obclient> SELECT * FROM information_schema.VIEW_TABLE_USAGE;
+--------------+-------------+-----------+--------------+------------+---------------+
| VIEW_CATALOG | VIEW_SCHEMA | VIEW_NAME | TABLE_SCHEMA | TABLE_NAME | TABLE_CATALOG |
+--------------+-------------+-----------+--------------+------------+---------------+
| def | test | view1 | test | tbl2 | def |
| def | test | view2 | test | view1 | def |
| def | test | view2 | test | tbl1 | def |
+--------------+-------------+-----------+--------------+------------+---------------+
3 rows in set
In the preceding example, the dependencies between database objects are returned in the query result of the view. For example, the view1 view references the tbl2 table, and the view2 view references the tbl1 table and the view1 view.
The dependencies of views on views and views on tables are useful in some scenarios. For example, when you migrate data between databases, you must recreate schema objects in the destination database. If the schema objects to be recreated are included in a view, you must first recreate the objects on which the view depends. Otherwise, an error message is returned when you recreate the view, indicating that the dependent objects do not exist. If you can obtain the dependencies of views on views and views on tables, you can recreate the schema objects with ease.