Database Tutorial

Discover Relationships between Tables

The first place to start is an Entity Relationship Diagram (ERD), like the one that introduces the Video Store ERD introduced in the Reading for week 4. In that same week’s material, you can learn how to read relationships from a diagram by reviewing the Dependencies web page.

SQL Developer provides the easiest way to discover relationships between tables when the development instance uses foreign keys to support referential integrity.

You navigate through the upper left Connections frame. You start at the top of the tree with the Connection menu, choose the Database menu option, and then the Tables menu option. It will list the tables in the database.

Click on a table like system_user and you will see the Columns subtab, which shows you the table definition. Underneath the major Columns tab you will see a series of subtabs. Click on the Constraints subtab and you will see all the constraints on the table in a scrollable window.

Naturally, this information is stored in the data dictionary. SQL Developer displays these values from the data dictionary. You can write a query to discover the same information. The syntax for this type of relationship discover is provided throughout Lab 2 where you create tables.

You can discover referential integrity by querying the foreign key constraints, like this:

SET PAGESIZE 999
COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A40 HEADING "References:| Table.Column"
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.position = ucc2.position -- Correction for multiple column primary keys.
AND      uc.constraint_type = 'R'
AND      ucc1.table_name = 'SYSTEM_USER'
ORDER BY ucc1.table_name
,        uc.constraint_name;

It would return the following formatted output:

Constraint Name:                       References:
 Table.Column                           Table.Column
-------------------------------------- ----------------------------------------
FK_SYSTEM_USER_1                       REFERENCES
(SYSTEM_USER.CREATED_BY)               (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_SYSTEM_USER_2                       REFERENCES
(SYSTEM_USER.LAST_UPDATED_BY)          (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_SYSTEM_USER_3                       REFERENCES
(SYSTEM_USER.SYSTEM_USER_GROUP_ID)     (COMMON_LOOKUP.COMMON_LOOKUP_ID)
 
FK_SYSTEM_USER_4                       REFERENCES
(SYSTEM_USER.SYSTEM_USER_TYPE)         (COMMON_LOOKUP.COMMON_LOOKUP_ID)

You notice that the COL[umn] SQL*Plus command along with the CHR(10) or line return formats the output. As a rule, most new developers rely on GUI tools, like SQL*Developer.