×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Identifying columns constraints

Identifying columns constraints

Identifying columns constraints

(OP)
Background:
We have a Oracle 8i database created using scripts provided by the US Army Corps of Engineers.  The scripts create a large number of tables (1700+) that are used to store data for a Geographic Information System or GIS.  

A lot of the columns in these tables are supposed to use only the allowed values as defined by other tables that are collectively referred to as Domain Tables or sometimes Picklists.  There are several hundred of these domain tables and presumably several hundred columns in the schema that tie to these tables.  

Issue:
I have been tasked to develop an interface for this schema.  One of the things I need to be able to do via a select statement (or multiple select statements) is to determine if a column has a domain table tied to it, and if it does what the name of that table is.  

I've looked around in the dictionary but can't find any way of getting what I need from there.  Does any one have any suggestions?

RE: Identifying columns constraints

Assuming these columns have foreign keys to those domain tables, then you can probe the data dictionary to determine the exact names.  Otherwise you cannot.
The most useful dictionary view is: user_constraints.

SQL>desc user_constraints
 Name                            Null?    Type
 ------------------------------- -------- ----
 OWNER                           NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME                 NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                          VARCHAR2(1)
 TABLE_NAME                      NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                         LONG
 R_OWNER                                  VARCHAR2(30)
 R_CONSTRAINT_NAME                        VARCHAR2(30)
 .....



Assuming you have these 2 small tables T1 and T2.  T1 would be considered you Domain table.  Table T2 has foreign key pointing to T1.

drop table t2;
drop table t1;

create table t1 (c1 number,
        constraint t1pk primary key (c1));

create table t2 (c1 number constraint fkt2 references t1(c1),
        constraint t2pk primary key (c1));

Now you can write this query to determine their relationship:

column table_name        format a10
column domain_table      format a20
column constraint_name   format a15
column r_constraint_name format a20

select  uc.table_name,
        uc.constraint_name,
        uc.r_constraint_name,
        uc2.table_name
from    
        user_constraints        uc,
        user_constraints        uc2
where   
        uc.constraint_type      = 'R'   /* Referential Integrity */
and     uc.status               = 'ENABLED'
and     uc2.status              = 'ENABLED'
and     uc.r_constraint_name    = uc2.constraint_name
and     uc.table_name           <>uc2.table_name /*no self-reference*/
and     uc.owner                = user
and     uc2.owner               = user;

The result would be:

TABLE_NAME CONSTRAINT_NAME R_CONSTRAINT_NAME    DOMAIN_TABLE
---------- --------------- -------------------- ----------
T2         FKT2            T1PK                 T1


This example assumes there is no self-referencing foreign keys (hence the extrace where clause above).  Otherwise you have to do extra coding.

RE: Identifying columns constraints

Hi Mike_Bui <grin>

You can come here *anytime* LOL

Nice script....

Mike
michael.j.lacey@ntlworld.com
Cargill's Corporate Web Site
Please -- Don't send me email questions without posting them in Tek-Tips as well. Better yet -- Post the question in Tek-Tips and send me a note saying "Have a look at so-and-so in the thingy forum would you?"

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close