The relationships and references between database objects are known as dependencies. This topic describes how to create and view dependencies between database objects in OceanBase Database's MySQL mode.
Some types of schema objects can reference other objects in their definitions. For example, a view can be defined as a query that references a table or another view. If object B is referenced in the definition of object A, A is a dependent object of B, and B is a referenced object of A.
The dependency relationships between views and tables, as well as views and other views, are described in a MySQL tenant. These relationships can be queried 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 dependency relationships between views and tables, as well as views and other views, can be very useful in some scenarios. For example, in a database migration scenario, it may be necessary to recreate schema objects in another database. If the schema object that needs to be recreated is a view, it is necessary to recreate the other objects that the view depends on before recreating the view itself. Otherwise, an error will occur during the recreation process stating that the dependent objects do not exist. By obtaining the dependency relationships between views and tables, as well as views and other views, it becomes easier to complete the task of recreating the objects.