Identifying Relationships in Oracle 1

[bomayed]

Hi ,

How can someone , through SQL , or any other methods, identifies the relationships between tables in a database?

In other words, how do I know that a specific field is a forigen key in a nother table?


I need to know , because I'm suppose to work on a database that was created by someone else and I need to understand the structure of the database.

Thank you

 

[SantaMufasa]

Bomayed,

First, if you would like to see the hierarchical Primary Key-to-Foregin Key relationships that exist amongst your tables, I like to use my script (below), which I named "pk_fk.sql":

REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
set pagesize 500
set feedback off
drop table uc
/
create table uc as
select table_name, constraint_name,
r_constraint_name from user_constraints
where constraint_type in ('P','R')
/
drop table pk_fk
/
create table pk_fk as
select decode(table_name,prior table_name,'(recursive)',table_name) detail,
 prior table_name master
from uc
where prior table_name is not null
connect by prior constraint_name =
r_constraint_name
union
select
table_name detail, '' master
from uc
where not exists
 (select 'x' from user_constraints
 where uc.table_name = table_name
   and constraint_type = 'R')
/
set pagesize 0
select
lpad(' ',(level-1)*3) ||
detail
from pk_fk
where master <> detail or master is null
connect by prior detail = master
start with master is null
/
set echo on
set feedback on
set pagesize 35

Here are the results of running pk_fk.sql against one of my schemas (the Oracle Education sample database):

S_IMAGE
   S_PRODUCT
      S_INVENTORY
      S_ITEM
S_LONGTEXT
   S_PRODUCT
      S_INVENTORY
      S_ITEM
S_REGION
   S_CUSTOMER
      S_ORD
         S_ITEM
   S_DEPT
      S_EMP
         (recursive)
         S_CUSTOMER
            S_ORD
               S_ITEM
         S_ORD
            S_ITEM
         S_WAREHOUSE
            S_INVENTORY
   S_WAREHOUSE
      S_INVENTORY
S_TITLE
   S_EMP
      (recursive)
      S_CUSTOMER
         S_ORD
            S_ITEM
      S_ORD
         S_ITEM
      S_WAREHOUSE
         S_INVENTORY

This output results from "tree-walking" the declarative Foreign key-to-Primary Key relationships.

Now, If you wish to see the actual column names that are Primary Keys and Foreign Keys (and all other constraints on a table), I use my "stru.sql" script that is a "Super Describe": You get all the output you would for a SQL*Plus describe, plus for each declarative constraint its Constraint Type, its Name, its Enabled Status, precisely what the constraint enforces. Following is a sample invocation of my "stru.sql" script:

SQL> @stru
Enter table name: s_emp

Col Column             Data Type     [Constraint Type: Name: En-/Dis-abled]
 #  Name               and Length    and Enforcement
--- ------------------ ------------- -------------------------------------------
  1 ID                 NUMBER(7)     [CK:S_EMP_ID_NN:ENABLED] "ID" IS NOT NULL
                                     [PK:S_EMP_ID_PK:ENABLED] NOT NULL/UNIQUE
  2 LAST_NAME          VARCHAR2(25)  [CK:S_EMP_LAST_NAME_NN:ENABLED] "LAST_NAME"
                                     IS NOT NULL

  3 FIRST_NAME         VARCHAR2(25)
  4 USERID             VARCHAR2(8)   [UK:S_EMP_USERID_UK:ENABLED] UNIQUE
  5 START_DATE         DATE
  6 COMMENTS           VARCHAR2(255)
  7 MANAGER_ID         NUMBER(7)     [FK:S_EMP_MANAGER_ID_FK:ENABLED] Match :
                                     S_EMP_ID_PK

  8 TITLE              VARCHAR2(25)  [FK:S_EMP_TITLE_FK:ENABLED] Match :
                                     S_TITLE_TITLE_PK

  9 DEPT_ID            NUMBER(7)     [FK:S_EMP_DEPT_ID_FK:ENABLED] Match :
                                     S_DEPT_ID_PK

 10 SALARY             NUMBER(11,2)

Col Column             Data Type     [Constraint Type: Name: En-/Dis-abled]
 #  Name               and Length    and Enforcement
--- ------------------ ------------- -------------------------------------------
 11 COMMISSION_PCT     NUMBER(4,2)   [CK:S_EMP_COMMISSION_PCT_CK:ENABLED]
                                     commission_pct IN (10, 12.5, 15, 17.5, 20)

Another table (Y/N) ?
*********************************************************************************

To use my "stru.sql" script, you must first run a one-time-only script named "strumake.sql": this script creates a work table and a PL/SQL procedure that "stru.sql" uses.

One-time "strumake.sql" script:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor Dasages, LLC, makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
drop table temp_cons;
create table temp_cons as
select constraint_name, constraint_type, ' ' search_condition,
r_constraint_name, status
from user_constraints
where 1 = 2;
alter table temp_cons modify search_condition varchar(2000)
/
create or replace procedure prep_user_constraints (table_2_do in varchar)
  is
  cursor cons is
     select constraint_name,constraint_type,
            search_condition,r_constraint_name, status
            from user_constraints
            where table_name = upper(table_2_do);
  my_search_condition varchar(32767);
begin
  delete temp_cons;
  commit;
  for i in cons loop
     my_search_condition := i.search_condition;
     insert into temp_cons values
       (i.constraint_name,i.constraint_type,
        my_search_condition,i.r_constraint_name,i.status);
     commit;
  end loop;
end;
/

Regular-use script, "stru.sql":

REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. The author
REM makes no warranty regarding this script's fitness for any
REM industrial application or purpose nor is there any claim that
REM this or any similarly-distributed scripts are error free or
REM should be used for any purpose other than illustration.
REM **************************************************************
set echo off
set verify off
set feedback off
set linesize 80
set pagesize 23
break on "#" on a on b
accept x prompt "Enter table name: "
col a format a18 heading "Column|Name" word_wrapped
col b format a13 heading "Data Type|and Length"
col c format a43 heading "[Constraint Type: Name: En-/Dis-abled]|and Enforcement" word_wrapped
col "#" format 99 heading "Col| # "
exec prep_user_constraints('&x')
(select column_id "#",
        col.column_name a,
        col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
       '[' ||
           decode(con.constraint_type,'P','PK','R','FK','C','CK','U','UK')
           || ':' ||con.constraint_name
           || ':' ||con.status
           || '] ' ||
           decode(con.constraint_type,
              'P','NOT NULL/UNIQUE',
              'U','UNIQUE',
              'C',con.search_condition,
              'R','Match : '||r_constraint_name) c
from   temp_cons con,
       user_cons_columns con_col,
       user_tab_columns col
where  col.table_name      = upper('&x')
  and  con.constraint_name = con_col.constraint_name
  and  col.table_name      = con_col.table_name
  and  col.column_name     = con_col.column_name
  )
union
((select column_id "#",
         col.column_name a,
         col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
        ' ' c
 from user_tab_columns col
 where col.table_name = upper('&x'))
minus
(select column_id "#",
        col.column_name a,
        col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
        ' ' c
 from user_tab_columns col, user_cons_columns con_col
 where col.table_name  = upper('&x')
   and col.table_name  = con_col.table_name
   and col.column_name = con_col.column_name)
)
order by 1
/
accept again prompt "Another table (Y/N) ? "
set heading off
set pagesize 0
spool temp.sql
set termout off
select decode(upper('&again'),'Y','@stru') from dual;
set termout on
spool off
set heading on
set pagesize 23
set feedback on
@temp

Let us know if these scripts provide you with the results you needed.

Mufasa
(aka Dave of Sandy, Utah, USA @ 04:31 (23Sep04) UTC (aka "GMT" and "Zulu"), 21:31 (22Sep04) Mountain Time)

 

[kmfna]

Good LORD, thats a LOT of scripting...you're still the man, Mufasa! Good looking out!

Kevin

- "The truth hurts, maybe not as much as jumping on a bicycle with no seat, but it hurts.