Identifying columns constraints

[ChrisQuick]

Background:<br>We have a Oracle 8i database created using scripts provided by the US Army Corps of Engineers.&nbsp;&nbsp;The scripts create a large number of tables (1700+) that are used to store data for a Geographic Information System or GIS.&nbsp;&nbsp;<br><br>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.&nbsp;&nbsp;There are several hundred of these domain tables and presumably several hundred columns in the schema that tie to these tables.&nbsp;&nbsp;<br><br>Issue:<br>I have been tasked to develop an interface for this schema.&nbsp;&nbsp;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.&nbsp;&nbsp;<br><br>I've looked around in the dictionary but can't find any way of getting what I need from there.&nbsp;&nbsp;Does any one have any suggestions?<br><br>

 

[Guest_imported]

Assuming these columns have foreign keys to those domain tables, then you can probe the data dictionary to determine the exact names.&nbsp;&nbsp;Otherwise you cannot.<br>The most useful dictionary view is: user_constraints.<br><br><FONT FACE=monospace>SQL&gt;desc user_constraints<br>&nbsp;Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Null?&nbsp;&nbsp;&nbsp;&nbsp;Type<br>&nbsp;------------------------------- -------- ----<br>&nbsp;OWNER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NOT NULL VARCHAR2(30)<br>&nbsp;<font color=red>CONSTRAINT_NAME</font>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NOT NULL VARCHAR2(30)<br>&nbsp;CONSTRAINT_TYPE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VARCHAR2(1)<br>&nbsp;TABLE_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NOT NULL VARCHAR2(30)<br>&nbsp;SEARCH_CONDITION&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LONG<br>&nbsp;R_OWNER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VARCHAR2(30)<br>&nbsp;<font color=red>R_CONSTRAINT_NAME</font>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VARCHAR2(30)<br>&nbsp;.....<br></font><br><br><br>Assuming you have these 2 small tables T1 and T2.&nbsp;&nbsp;T1 would be considered you Domain table.&nbsp;&nbsp;Table T2 has foreign key pointing to T1.<br><FONT FACE=monospace><br>drop table t2;<br>drop table t1;<br><br>create table t1 (c1 number,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;constraint t1pk primary key (c1));<br><br>create table t2 (c1 number constraint fkt2 references t1(c1),<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;constraint t2pk primary key (c1));<br></font><br>Now you can write this query to determine their relationship:<br><FONT FACE=monospace><br>column table_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;format a10<br>column domain_table&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;format a20<br>column constraint_name&nbsp;&nbsp;&nbsp;format a15<br>column r_constraint_name format a20<br><br>select&nbsp;&nbsp;uc.table_name,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;uc.constraint_name,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;uc.r_constraint_name,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;uc2.table_name<br>from&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;user_constraints&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;uc,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;user_constraints&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;uc2<br>where&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;uc.constraint_type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= 'R'&nbsp;&nbsp;&nbsp;/* Referential Integrity */<br>and&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;uc.status&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= 'ENABLED'<br>and&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;uc2.status&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= 'ENABLED'<br>and&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;uc.r_constraint_name&nbsp;&nbsp;&nbsp;&nbsp;= uc2.constraint_name<br><font color=red>and&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;uc.table_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;&gt;uc2.table_name</font> /*no self-reference*/<br>and&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;uc.owner&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= user<br>and&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;uc2.owner&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;= user;<br></font><br>The result would be:<br><FONT FACE=monospace><br>TABLE_NAME CONSTRAINT_NAME R_CONSTRAINT_NAME&nbsp;&nbsp;&nbsp;&nbsp;DOMAIN_TABLE<br>---------- --------------- -------------------- ----------<br><font color=red>T2</font>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FKT2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;T1PK&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color=red>T1</font><br><br></font><br>This example assumes there is no self-referencing foreign keys (hence the extrace where clause above).&nbsp;&nbsp;Otherwise you have to do extra coding.

 

[MikeLacey]

Hi Mike_Bui &lt;grin&gt;<br><br>You can come here *anytime* LOL<br><br>Nice script.... <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>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?"