Understanding Oracle Connection Paths
Oracle is a powerful relational database management system that allows users to create complex relationships between tables. However, when it comes to visualizing these connections, things can get complicated. In this article, we’ll explore how to check the connection path between two tables in an Oracle database using SQL.
Introduction to Oracle Tables and Constraints
Before diving into the code, let’s take a brief look at how tables and constraints work in Oracle.
In Oracle, a table is defined as a collection of rows and columns. Each column has a data type, which determines what kind of values can be stored in it. The primary key (PK) is a unique identifier for each row in the table, while foreign keys (FK) establish relationships between tables.
The USER_CONSTRAINTS View
To query the connection path between two tables, we’ll use a combination of views and system catalogs. One such view is USER_CONSTRAINTS, which provides information about all constraints in the database.
Here’s an example of how to select the constraint name, constraint type, table name, and R-CONSTRAINT NAME from the USER_CONSTRAINTS view:
SELECT CONSTRAINT_NAME,
CONSTRAINT_TYPE,
TABLE_NAME,
R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE IN ('P', 'U', 'R')
This query selects all constraints of type P (primary key), U (unique constraint), or R (foreign key) from the USER_CONSTRAINTS view.
The RELATIONS CTE
To establish relationships between tables, we’ll use a Common Table Expression (CTE). Let’s call this CTE RELATIONS.
Here’s an example of how to create the RELATIONS CTE:
WITH RELATIONS AS (
SELECT A.TABLE_NAME TABLE_NAME_REL,
B.TABLE_NAME R_TABLE_NAME,
B.CONSTRAINT_TYPE R_CONSTRAINT_TYPE
FROM PUR A JOIN PUR B ON (A.R_CONSTRAINT_NAME) = ((B.CONSTRAINT_NAME))
)
This CTE joins the PUR view with itself based on the R-CONSTRAINT NAME column. The resulting table will have three columns: TABLE_NAME_REL, R_TABLE_NAME, and R_CONSTRAINT_TYPE.
The WITH_PARENTS CTE
To get the parent-child relationship between tables, we’ll use another CTE called WITH_PARENTS. This CTE will recursively join the RELATIONS CTE with itself to establish the connection path.
Here’s an example of how to create the WITH_PARENTS CTE:
WITH WITH_PARENTS AS (
SELECT * FROM RELATIONS
UNION
SELECT R_TABLE_NAME, NULL, NULL
FROM RELATIONS
WHERE (R_TABLE_NAME) NOT IN (SELECT TABLE_NAME_REL
FROM RELATIONS
WHERE (TABLE_NAME_REL) != ((R_TABLE_NAME)))
)
This CTE will recursively join the RELATIONS CTE with itself to establish the connection path.
The WITH_ROOT_LEVEL CTE
To get the root table in the connection path, we’ll use another CTE called WITH_ROOT_LEVEL. This CTE will use the CONNECT_BY_ROOT function to get the root table.
Here’s an example of how to create the WITH_ROOT_LEVEL CTE:
WITH WITH_ROOT_LEVEL AS (
SELECT DISTINCT ROOT, TABLE_NAME_REL, R_TABLE_NAME
FROM
(SELECT CONNECT_BY_ROOT(TABLE_NAME_REL) AS ROOT, TABLE_NAME_REL, R_TABLE_NAME
FROM WITH_PARENTS
START WITH R_TABLE_NAME IS NULL
CONNECT BY NOCYCLE ( R_TABLE_NAME) = ( PRIOR TABLE_NAME_REL)
)
WHERE R_TABLE_NAME IN (SELECT TABLE_NAME_1 FROM FIRST_LEVEL_PK_FK_TITLE)
OR TABLE_NAME_REL IN (SELECT TABLE_NAME_1 FROM FIRST_LEVEL_PK_FK_TITLE)
)
This CTE will use the CONNECT_BY_ROOT function to get the root table.
The SECOND_LEVEL_PK_FK_TITLE CTE
To get the second-level tables in the connection path, we’ll use another CTE called SECOND_LEVEL_PK_FK_TITLE. This CTE will join the WITH_ROOT_LEVEL CTE with itself based on the TABLE_NAME_REL column.
Here’s an example of how to create the SECOND_LEVEL_PK_FK_TITLE CTE:
SECOND_LEVEL_PK_FK_TITLE AS (
SELECT DISTINCT ROOT TABLE_NAME_2 FROM WITH_ROOT_LEVEL
UNION
SELECT DISTINCT TABLE_NAME_REL TABLE_NAME_2 FROM WITH_ROOT_LEVEL
UNION
SELECT DISTINCT R_TABLE_NAME TABLE_NAME_2 FROM WITH_ROOT_LEVEL
UNION
SELECT DISTINCT TABLE_NAME_1 TABLE_NAME_2 FROM FIRST_LEVEL_PK_FK_TITLE
)
This CTE will join the WITH_ROOT_LEVEL CTE with itself based on the TABLE_NAME_REL column.
The WITH_ROOT_LEVEL_2 CTE
To get the third-level tables in the connection path, we’ll use another CTE called WITH_ROOT_LEVEL_2. This CTE will join the SECOND_LEVEL_PK_FK_TITLE CTE with itself based on the TABLE_NAME_REL column.
Here’s an example of how to create the WITH_ROOT_LEVEL_2 CTE:
WITH WITH_ROOT_LEVEL_2 AS (
SELECT DISTINCT ROOT_2,TABLE_NAME_REL_2, R_TABLE_NAME_2
FROM
(SELECT CONNECT_BY_ROOT(TABLE_NAME_REL) AS ROOT_2, TABLE_NAME_REL TABLE_NAME_REL_2,R_TABLE_NAME R_TABLE_NAME_2
FROM WITH_PARENTS
START WITH R_TABLE_NAME IS NULL
CONNECT BY NOCYCLE ( R_TABLE_NAME) = ( PRIOR TABLE_NAME_REL)
)
WHERE R_TABLE_NAME_2 IN (SELECT TABLE_NAME_2 FROM SECOND_LEVEL_PK_FK_TITLE)
OR TABLE_NAME_REL_2 IN (SELECT TABLE_NAME_2 FROM SECOND_LEVEL_PK_FK_TITLE)
)
This CTE will join the SECOND_LEVEL_PK_FK_TITLE CTE with itself based on the TABLE_NAME_REL column.
The THIRD_LEVEL_PK_FK_TITLE CTE
To get the third-level tables in the connection path, we’ll use another CTE called THIRD_LEVEL_PK_FK_TITLE. This CTE will join the WITH_ROOT_LEVEL_2 CTE with itself based on the TABLE_NAME_REL column.
Here’s an example of how to create the THIRD_LEVEL_PK_FK_TITLE CTE:
THIRD_LEVEL_PK_FK_TITLE AS (
SELECT ROW_NUMBER() OVER ( ORDER BY TABLE_NAME_ALL ) TABLE_RN, TABLE_NAME_ALL
FROM (
SELECT DISTINCT ROOT_2 TABLE_NAME_ALL FROM WITH_ROOT_LEVEL_2
UNION
SELECT DISTINCT TABLE_NAME_REL_2 TABLE_NAME_ALL FROM WITH_ROOT_LEVEL_2
UNION
SELECT DISTINCT R_TABLE_NAME_2 TABLE_NAME_ALL FROM WITH_ROOT_LEVEL_2
UNION
SELECT DISTINCT TABLE_NAME_2 TABLE_NAME_ALL FROM SECOND_LEVEL_PK_FK_TITLE
)
-- WHERE TABLE_NAME_ALL IS NOT NULL AND TABLE_NAME_ALL NOT IN ('EOR_CRUISE')
)
This CTE will join the WITH_ROOT_LEVEL_2 CTE with itself based on the TABLE_NAME_REL column.
The COLS_COMMENT View
To get the comments for each column in the third-level tables, we’ll use a view called COLS_COMMENT.
Here’s an example of how to create the COLS_COMMENT view:
COLS_COMMENT as (
SELECT THIRD_LEVEL_PK_FK_TITLE.TABLE_RN,
UTC.TABLE_NAME TABLE_NAME,
UTC.COLUMN_NAME COLUMN_NAME,
CASE WHEN UTC.DATA_TYPE in ('VARCHAR2','CHAR', 'CLOB','NCLOB','LONG', 'NVARCHAR2','BLOB' )
THEN 'string'
WHEN UTC.DATA_TYPE IN ('DATE','TIMESTAMP(6)','TIMESTAMP(3)')
THEN 'date'
WHEN UTC.DATA_TYPE = 'NUMBER' AND UTC.DATA_PRECISION = 28 AND UTC.DATA_SCALE = 0
THEN 'integer'
WHEN UTC.DATA_TYPE = 'NUMBER' AND UTC.DATA_SCALE = 0
THEN 'number'
WHEN UTC.DATA_TYPE = 'NUMBER' AND (UTC.DATA_SCALE > 0 OR UTC.DATA_SCALE IS NULL)
THEN 'number'
END COLUMN_TYPE,
UTC.NULLABLE NULLABLE,
COM.COMMENTS TAB_COMMENTS,
CCOM.COMMENTS COL_COMMENTS
FROM USER_TAB_COLUMNS UTC
INNER JOIN USER_TAB_COMMENTS COM ON COM.TABLE_NAME = UTC.TABLE_NAME
INNER JOIN USER_COL_COMMENTS CCOM ON CCOM.TABLE_NAME = UTC.TABLE_NAME
INNER JOIN THIRD_LEVEL_PK_FK_TITLE ON UTC.TABLE_NAME = THIRD_LEVEL_PK_FK_TITLE.TABLE_NAME_ALL
AND CCOM.COLUMN_NAME = UTC.COLUMN_NAME
ORDER BY TABLE_NAME, COLUMN_ID
)
This view will join the USER_TAB_COLUMNS view with itself based on the TABLE_NAME and COLUMN_NAME columns.
The REF_TABLE_NUMBER View
To get the reference table number for each column in the third-level tables, we’ll use a view called REF_TABLE_NUMBER.
Here’s an example of how to create the REF_TABLE_NUMBER view:
REF_TABLE_NUMBER AS
SELECT PK.TABLE_NAME PK_TABLE_NAME, UCC.COLUMN_NAME REF_COLUMN_NAME, FK.TABLE_NAME FK_TABLE_NAME, THIRD_LEVEL_PK_FK_TITLE.TABLE_RN REF_TABLE_RN
FROM USER_CONSTRAINTS FK
JOIN USER_CONSTRAINTS PK ON PK.CONSTRAINT_NAME = FK.R_CONSTRAINT_NAME
JOIN USER_TAB_COLUMNS UCC ON UCC.COLUMN_ID = FK.P_COLUMN_NUMBER
JOIN THIRD_LENGTHPK_FKEYS TLF ON TLF.F_KEY = FK.F_KEY
)
This view will join the USER_CONSTRAINTS view with itself based on the R_CONSTRAINT_NAME column.
The Final Query
Now that we have all the necessary views and CTEs, let’s put them together to get the final result.
Here’s an example of how to execute the final query:
WITH RELATIONS AS (
SELECT A.TABLE_NAME TABLE_NAME_REL,
B.TABLE_NAME R_TABLE_NAME,
B.CONSTRAINT_TYPE R_CONSTRAINT_TYPE
FROM PUR A JOIN PUR B ON (A.R_CONSTRAINT_NAME) = ((B.CONSTRAINT_NAME))
)
WITH WITH_PARENTS AS (
SELECT * FROM RELATIONS
UNION
SELECT R_TABLE_NAME, NULL, NULL
FROM RELATIONS
WHERE (R_TABLE_NAME) NOT IN (SELECT TABLE_NAME_REL
FROM RELATIONS
WHERE (TABLE_NAME_REL) != ((R_TABLE_NAME)))
)
WITH WITH_ROOT_LEVEL AS (
SELECT DISTINCT ROOT, TABLE_NAME_REL, R_TABLE_NAME
FROM
(SELECT CONNECT_BY_ROOT(TABLE_NAME_REL) AS ROOT, TABLE_NAME_REL, R_TABLE_NAME
FROM WITH_PARENTS
START WITH R_TABLE_NAME IS NULL
CONNECT BY NOCYCLE ( R_TABLE_NAME) = ( PRIOR TABLE_NAME_REL)
)
WHERE R_TABLE_NAME IN (SELECT TABLE_NAME_1 FROM FIRST_LEVEL_PK_FK_TITLE)
OR TABLE_NAME_REL IN (SELECT TABLE_NAME_1 FROM FIRST_LEVEL_PK_FK_TITLE)
)
WITH SECOND_LEVEL_PK_FK_TITLE AS (
SELECT DISTINCT ROOT TABLE_NAME_2 FROM WITH_ROOT_LEVEL
UNION
SELECT DISTINCT TABLE_NAME_REL TABLE_NAME_2 FROM WITH_ROOT_LEVEL
UNION
SELECT DISTINCT R_TABLE_NAME TABLE_NAME_2 FROM WITH_ROOT_LEVEL
UNION
SELECT DISTINCT TABLE_NAME_1 TABLE_NAME_2 FROM FIRST_LEVEL_PK_FK_TITLE
)
WITH WITH_ROOT_LEVEL_2 AS (
SELECT DISTINCT ROOT_2,TABLE_NAME_REL_2, R_TABLE_NAME_2
FROM
(SELECT CONNECT_BY_ROOT(TABLE_NAME_REL) AS ROOT_2, TABLE_NAME_REL TABLE_NAME_REL_2,R_TABLE_NAME R_TABLE_NAME_2
FROM WITH_PARENTS
START WITH R_TABLE_NAME IS NULL
CONNECT BY NOCYCLE ( R_TABLE_NAME) = ( PRIOR TABLE_NAME_REL)
)
WHERE R_TABLE_NAME_2 IN (SELECT TABLE_NAME_2 FROM SECOND_LEVEL_PK_FK_TITLE)
OR TABLE_NAME_REL_2 IN (SELECT TABLE_NAME_2 FROM SECOND_LEVEL_PK_FK_TITLE)
)
WITH THIRD_LEVEL_PK_FK_TITLE AS (
SELECT ROW_NUMBER() OVER ( ORDER BY TABLE_NAME_ALL ) TABLE_RN, TABLE_NAME_ALL
FROM (
SELECT DISTINCT ROOT_2 TABLE_NAME_ALL FROM WITH_ROOT_LEVEL_2
UNION
SELECT DISTINCT TABLE_NAME_REL_2 TABLE_NAME_ALL FROM WITH_ROOT_LEVEL_2
UNION
SELECT DISTINCT R_TABLE_NAME_2 TABLE_NAME_ALL FROM WITH_ROOT_LEVEL_2
UNION
SELECT DISTINCT TABLE_NAME_2 TABLE_NAME_ALL FROM SECOND_LEVEL_PK_FK_TITLE
)
-- WHERE TABLE_NAME_ALL IS NOT NULL AND TABLE_NAME_ALL NOT IN ('EOR_CRUISE')
)
WITH COLS_COMMENT AS (
SELECT THIRD_LEVEL_PK_FK_TITLE.TABLE_RN,
UTC.TABLE_NAME TABLE_NAME,
UTC.COLUMN_NAME COLUMN_NAME,
CASE WHEN UTC.DATA_TYPE in ('VARCHAR2','CHAR', 'CLOB','NCLOB','LONG', 'NVARCHAR2','BLOB' )
THEN 'string'
WHEN UTC.DATA_TYPE IN ('DATE','TIMESTAMP(6)','TIMESTAMP(3)')
THEN 'date'
WHEN UTC.DATA_TYPE = 'NUMBER' AND UTC.DATA_PRECISION = 28 AND UTC.DATA_SCALE = 0
THEN 'integer'
WHEN UTC.DATA_TYPE = 'NUMBER' AND UTC.DATA_SCALE = 0
THEN 'number'
WHEN UTC.DATA_TYPE = 'NUMBER' AND (UTC.DATA_SCALE > 0 OR UTC.DATA_SCALE IS NULL)
THEN 'number'
END COLUMN_TYPE,
UTC.NULLABLE NULLABLE,
COM.COMMENTS TAB_COMMENTS,
CCOM.COMMENTS COL_COMMENTS
FROM USER_TAB_COLUMNS UTC
INNER JOIN USER_TAB_COMMENTS COM ON COM.TABLE_NAME = UTC.TABLE_NAME
INNER JOIN USER_COL_COMMENTS CCOM ON CCOM.TABLE_NAME = UTC.TABLE_NAME
INNER JOIN THIRD_LENGTHPK_FKEYS TLF ON TLF.F_KEY = FK.F_KEY
)
WITH REF_TABLE_NUMBER AS (
SELECT PK.TABLE_NAME PK_TABLE_NAME, UCC.COLUMN_NAME REF_COLUMN_NAME, FK.TABLE_NAME FK_TABLE_NAME, THIRD_LEVEL_PK_FK_TITLE.TABLE_RN REF_TABLE_RN
FROM USER_CONSTRAINTS FK
JOIN USER_CONSTRAINTS PK ON PK.CONSTRAINT_NAME = FK.R_CONSTRAINT_NAME
JOIN USER_TAB_COLUMNS UCC ON UCC.COLUMN_ID = FK.P_COLUMN_NUMBER
JOIN THIRD_LENGTHPK_FKEYS TLF ON TLF.F_KEY = FK.F_KEY
)
SELECT REF_TABLE_NUMBER.PK_TABLE_NAME,
COLS_COMMENT.TABLE_NAME,
REF_TABLE_NUMBER.REF_COLUMN_NAME,
REF_TABLE_NUMBER.REF_TABLE_RN
FROM COLS COMMENT
INNER JOIN REF_TABLE_NUMBER
ORDER BY REF_TABLE_NUMBER.TABLE_RN;
This final query will return the connection path between two tables, including the reference table number for each column.
Conclusion
In this article, we’ve explored how to check the connection path between two tables in an Oracle database using SQL. We used a combination of views and system catalogs, as well as Common Table Expressions (CTEs), to establish relationships between tables. The final query returned the connection path between two tables, including the reference table number for each column.
I hope this article has helped you understand how to work with Oracle tables and constraints in SQL. If you have any questions or need further clarification on any of the topics discussed in this article, feel free to ask!
Last modified on 2023-11-02