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?
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?
Talk To Other Members
RE: Identifying columns constraints
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
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?"