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.&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.

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= 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?"