Re: Help with Dictionary Views: confirming FK relationship

From: <fitzjarrell_at_cox.net>
Date: 1 May 2006 20:04:37 -0700
Message-ID: <1146539077.694184.274110@i39g2000cwa.googlegroups.com>

Comments embedded.
BD wrote:
> Hi, all.
>
> I'm on 8.1.7.4 on AIX.
>
> I have a task to identify tables in my database that lack two specific
> columns (figured that one out), and which lack a FK relationship to any
> tables that have those columns.
>
> In any "sane" FK relationship, the column names on both sides of the
> relationship will be the same, so the first query would be all I would
> need. But, strictly speaking, it is not mandatory. I could have an FK
> relationship from TABLE1.COLUMN1 to TABLE2.SPUD if I wanted to.
>

You're saying, then that columns in PK/FK relationships which are NOT named exactly the same is the result of some insanity on the part of the development team or the coding standards committee. As an example:

create table customer (

cst_id  number not null,
cst_name        varchar2(40) not null,
cst_status      varchar2(10),
cst_cred_rating number,

constraint customer_pk
primary key(cst_id));

create table orders (

ord_id number not null.
ord_cst_id number not null.
ord_dt  date,
ord_ttl_price   number(9,2),
ord_status      varchar2(12),

constraint orders_pk
primary key(ord_id),
constraint orders_cust_fk
foreign_key(ord_cst_id) references customer);

You're saying the ord_cst_id column as a foreign key is ... insanity ... as it's not named exactly the same as the PK column in the parent table. I disagree. It may not make YOUR life easier, but it is still a SANE foreign key relationship.

> So I need to find out which tables have columns A and B, and which
> tables have any FK relationships against those tables, on columns A and
> B.
>
> dba_constraints and dba_cons_columns will tell me about the 'child'
> side of the FK relationship, but I need to confirm the 'parent' side of
> the FK relationship; I don't see a sys view which indicates _both_
> sides of the relationship.
>
> Does this information exist anywhere in the dictionary views?
>
> Thanks,
>
> BD

Mark C. Stock provided the answer to your query, and I have nothing more to add to that. I take exception to your 'identically named columns are the only sane foreign key relationships' comment, however.  Valid naming conventions within an organisation can, and do, cause your statement to be false.

David Fitzjarrell Received on Mon May 01 2006 - 22:04:37 CDT