Skip to Main Content
  • Questions

  • Dictionary views - data on column constraints

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jack.

Asked: March 28, 2001 - 7:55 pm UTC

Last updated: September 22, 2003 - 11:03 am UTC

Version: pl/sql 2.3

Viewed 1000+ times

You Asked

I am interested in finding out the constraints on table and view columns (eg, if they are primary keys, foreign keys and what table/columns are referenced, any default values, if they are constants, any check constraints for the column, and if they reference a v. 8.0 nested column). Though the dictionary views USER_TABLES, _TAB_COLUMNS AND _CONS_COLUMNS give some information on column constraints (eg, if null, data type and size), I could not find any views for the particular information I needed and have listed above. Could you tell me where I might find this information ?

Thanks, Jack



and Tom said...

1) primary keys on a table:


scott@TKYTE816> select a.constraint_name, b.column_name
2 from user_constraints a, user_cons_columns b
3 where a.constraint_type = 'P' and a.constraint_name = b.constraint_name
4 and a.table_name = 'EMP'
5 order by b.position
6 /

CONSTRAINT_NAME COLUMN_NAME
------------------------------ ------------------------------
EMP_PK EMPNO

scott@TKYTE816> 4
4* and a.table_name = 'EMP'
scott@TKYTE816> c/EMP/DEPT
4* and a.table_name = 'DEPT'
scott@TKYTE816> /

CONSTRAINT_NAME COLUMN_NAME
------------------------------ ------------------------------
DEPT_PK DEPTNO



2) foreign keys -- this query is overkill for you but I had it lying around. It generates the foreign key statements given a parent table (i use it to drop and rebuild constraints for a truncate or something like that)


scott@TKYTE816> @cons emp
scott@TKYTE816> column fkey format a80 word_wrapped
scott@TKYTE816> select
2 'alter table "' || child_tname || '"' || chr(10) ||
3 'add constraint "' || child_cons_name || '"' || chr(10) ||
4 'foreign key ( ' || child_columns || ' ) ' || chr(10) ||
5 'references "' || parent_tname || '" ( ' || parent_columns || ');' fkey
6 from
7 ( select a.table_name child_tname, a.constraint_name child_cons_name,
8 b.r_constraint_name parent_cons_name,
9 max(decode(position, 1, '"'||
10 substr(column_name,1,30)||'"',NULL)) ||
11 max(decode(position, 2,', '||'"'||
12 substr(column_name,1,30)||'"',NULL)) ||
13 max(decode(position, 3,', '||'"'||
14 substr(column_name,1,30)||'"',NULL)) ||
15 max(decode(position, 4,', '||'"'||
16 substr(column_name,1,30)||'"',NULL)) ||
17 max(decode(position, 5,', '||'"'||
18 substr(column_name,1,30)||'"',NULL)) ||
19 max(decode(position, 6,', '||'"'||
20 substr(column_name,1,30)||'"',NULL)) ||
21 max(decode(position, 7,', '||'"'||
22 substr(column_name,1,30)||'"',NULL)) ||
23 max(decode(position, 8,', '||'"'||
24 substr(column_name,1,30)||'"',NULL)) ||
25 max(decode(position, 9,', '||'"'||
26 substr(column_name,1,30)||'"',NULL)) ||
27 max(decode(position,10,', '||'"'||
28 substr(column_name,1,30)||'"',NULL)) ||
29 max(decode(position,11,', '||'"'||
30 substr(column_name,1,30)||'"',NULL)) ||
31 max(decode(position,12,', '||'"'||
32 substr(column_name,1,30)||'"',NULL)) ||
33 max(decode(position,13,', '||'"'||
34 substr(column_name,1,30)||'"',NULL)) ||
35 max(decode(position,14,', '||'"'||
36 substr(column_name,1,30)||'"',NULL)) ||
37 max(decode(position,15,', '||'"'||
38 substr(column_name,1,30)||'"',NULL)) ||
39 max(decode(position,16,', '||'"'||
40 substr(column_name,1,30)||'"',NULL))
41 child_columns
42 from user_cons_columns a, user_constraints b
43 where a.constraint_name = b.constraint_name
44 and b.constraint_type = 'R'
45 group by a.table_name, a.constraint_name, b.r_constraint_name ) child,
46 ( select a.constraint_name parent_cons_name, a.table_name parent_tname,
47 max(decode(position, 1, '"'||
48 substr(column_name,1,30)||'"',NULL)) ||
49 max(decode(position, 2,', '||'"'||
50 substr(column_name,1,30)||'"',NULL)) ||
51 max(decode(position, 3,', '||'"'||
52 substr(column_name,1,30)||'"',NULL)) ||
53 max(decode(position, 4,', '||'"'||
54 substr(column_name,1,30)||'"',NULL)) ||
55 max(decode(position, 5,', '||'"'||
56 substr(column_name,1,30)||'"',NULL)) ||
57 max(decode(position, 6,', '||'"'||
58 substr(column_name,1,30)||'"',NULL)) ||
59 max(decode(position, 7,', '||'"'||
60 substr(column_name,1,30)||'"',NULL)) ||
61 max(decode(position, 8,', '||'"'||
62 substr(column_name,1,30)||'"',NULL)) ||
63 max(decode(position, 9,', '||'"'||
64 substr(column_name,1,30)||'"',NULL)) ||
65 max(decode(position,10,', '||'"'||
66 substr(column_name,1,30)||'"',NULL)) ||
67 max(decode(position,11,', '||'"'||
68 substr(column_name,1,30)||'"',NULL)) ||
69 max(decode(position,12,', '||'"'||
70 substr(column_name,1,30)||'"',NULL)) ||
71 max(decode(position,13,', '||'"'||
72 substr(column_name,1,30)||'"',NULL)) ||
73 max(decode(position,14,', '||'"'||
74 substr(column_name,1,30)||'"',NULL)) ||
75 max(decode(position,15,', '||'"'||
76 substr(column_name,1,30)||'"',NULL)) ||
77 max(decode(position,16,', '||'"'||
78 substr(column_name,1,30)||'"',NULL))
79 parent_columns
80 from user_cons_columns a, user_constraints b
81 where a.constraint_name = b.constraint_name
82 and b.constraint_type in ( 'P', 'U' )
83 group by a.table_name, a.constraint_name ) parent
84 where child.parent_cons_name = parent.parent_cons_name
85 and parent.parent_tname = upper('&1')
86 /
old 85: and parent.parent_tname = upper('&1')
new 85: and parent.parent_tname = upper('emp')

FKEY
--------------------------------------------------------------------------------
alter table "EMP"
add constraint "EMP_FK_EMP"
foreign key ( "MGR" )
references "EMP" ( "EMPNO");


3) default values:

scott@TKYTE816> select column_name, data_default
2 from user_tab_columns
3 where table_name = 'EMP'
4 and data_default is not null;

no rows selected

scott@TKYTE816> alter table emp modify sal default 100;

Table altered.

scott@TKYTE816> select column_name, data_default
2 from user_tab_columns
3 where table_name = 'EMP'
4 and data_default is not null;

COLUMN_NAME
------------------------------
DATA_DEFAULT
------------------------------------------------------------
SAL
100


4) constraints:

scott@TKYTE816> select constraint_name, constraint_type, search_condition
2 from user_constraints
3 where table_name = 'EMP'
4 /

CONSTRAINT_NAME C SEARCH_CONDITION
------------------------------ - ------------------------------
SYS_C003382 C "EMPNO" IS NOT NULL
EMP_PK P
EMP_FK_DEPT R
EMP_FK_EMP R
SAL_CHK C sal < 10000

you could add where constraint_type = 'C' just to get check constraints.



Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Table names based upon FK relationship

A reader, April 20, 2003 - 6:02 pm UTC

Hi Tom,
How can I find the table names from dictionary views based upon FK relationship? For example, I have a hierarchy of area to region to state etc. Area has FK to region and region has FK to state and to.... If I now table name region, I want to get table name area, and if I know table name state, I want to get table names region and area. The depth of hierarchies is different for different set of hierarchies (as there are lots of hierarchies - time, customer, salesrep etc.). However, there are FKs to PKs existing for all hierarchies.
Thanks


Tom Kyte
April 20, 2003 - 6:18 pm UTC 

you would write queries against user_constraints.  it is all in there -- constraint_type = 'R' is foreign keys.

select * from user_constraints where constraint_type= 'R' and table_name = 'X'

gives you the fkeys for a table, do a join of that 


select 
  from user_constraints a, user_constraints b
 where a.constraint_type = 'R' 
   and a.table_name = 'X'
   and a.r_constraint_name = b.constraint_name


and you have the fkeys from X to whatever table (and the table they point to)

If you are lucky enough to be on 9i where you can connect by a join, you can even do the hierarchy thing.


ops$tkyte@ORA920> create table t0 ( x int constraint t0_pk primary key );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create table t1 ( x int constraint t1_pk primary key, y references t0 );

Table created.

ops$tkyte@ORA920> create table t2 ( x int constraint t2_pk primary key, y references t1 );

Table created.

ops$tkyte@ORA920> create table t3 ( x int constraint t3_pk primary key, y references t2 );

Table created.

ops$tkyte@ORA920> create table t4 ( x int constraint t4_pk primary key, y references t2 );

Table created.

ops$tkyte@ORA920> create table t5 ( x int constraint t5_pk primary key, y references t4 );

Table created.

ops$tkyte@ORA920> create table t6 ( x int constraint t6_pk primary key, y references t5 );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> column table_name format a30
ops$tkyte@ORA920> select rpad( '*', 2*level, '*' ) || table_name table_name
  2    from (select a.table_name,
  3                 a.constraint_name,
  4                             a.r_constraint_name,
  5                             a.constraint_type,
  6                 b.constraint_name primary_key
  7                    from user_constraints a, user_constraints b
  8                   where a.constraint_type in ('P', 'R' )
  9                     and b.constraint_type = 'P'
 10                     and a.table_name = b.table_name )
 11   start with ( table_name = 'T0' and constraint_type = 'P' )
 12  connect by prior primary_key = r_constraint_name
 13  /

TABLE_NAME
------------------------------
**T0
****T1
******T2
********T3
********T4
**********T5
************T6

7 rows selected.

Thanks a lot!

A reader, April 20, 2003 - 6:39 pm UTC

Tom,
I was quite lucky to get reply with in 10 minutes of posting a question, you are really like a "superman".
Questions:
1. I have seen that normally you use in-line views instead of self referencing tables. But in this reply (first query) you did not use in-line views. Is there any specific reason???
2. We are on 8.1.7.3. Is there any alternative approach to second query, as this is what exactly I am looking for.
Thanks

Tom Kyte
April 20, 2003 - 8:11 pm UTC

1) don't understand what you mean by "self referencing tables" in this context.

I used inline views all over the place on this page -- when I needed to. which query exactly did you think would benefit from inline views over what I did?

2) You'll have to two step it.

create global temporary table TEMP
as
select a.table_name,
a.constraint_name,
a.r_constraint_name,
a.constraint_type,
b.constraint_name primary_key
from user_constraints a, user_constraints b
where 1=0;



do that once and then:

insert into temp
select a.table_name,
a.constraint_name,
a.r_constraint_name,
a.constraint_type,
b.constraint_name primary_key
from user_constraints a, user_constraints b
where a.constraint_type in ('P', 'R' )
and b.constraint_type = 'P'
and a.table_name = b.table_name ;


select rpad( '*', 2*level, '*' ) || table_name table_name
from temp
start with ( table_name = 'T0' and constraint_type = 'P' )
connect by prior primary_key = r_constraint_name
;


Why I am getting this error..

A reader, April 20, 2003 - 9:55 pm UTC 

Hi Tom,
Could you please help me in resolving this error?
SQL>  create or replace procedure test1_proc
  2   as
  3   begin
  4   EXECUTE IMMEDIATE 'create global temporary table temp_constraints
  5      as select a.table_name,
  6              a.constraint_name pkey_constraint,
  7                  b.constraint_name fkey_constraint,
  8                  b.r_constraint_name
  9         from user_constraints a, user_constraints b
 10        where 1=0';
 11  end;
 12  /

Procedure created.

SQL> exec test1_proc
BEGIN test1_proc; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "user.TEST1_PROC", line 4
ORA-06512: at line 1

The user created the procedure is same as user executing the procedure.
Kind Regards,

Tom Kyte
April 21, 2003 - 7:08 am UTC

well -- #1 question from me is "why would you do that"

a global temporary table is like "EMP", you create it ONCE per database, no more, no less. It is really something that doesn't belong in a stored procedure. My fear is you are in the current mindset that "sqlserver/informix/whatever makes me create a temp table in the procedure, use it and then they drop it -- that's what I need to do in Oracle". It doesn't work that way.

You create the gtt ONCE -- not once per use (it would be deadly to create it over and over and over)....

That aside, read
</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

for why you are getting the 1031. but - after reading that -- change your approach anyway and don't do this.

Procedure works

A reader, April 21, 2003 - 1:10 am UTC

Hi,
Sorry to bother you, the procedure works by giving explicit privileges to the user.
Regards


Tom Kyte
April 21, 2003 - 7:18 am UTC

but DON'T USE IT!

Thanks for advice.

A reader, April 21, 2003 - 6:58 pm UTC

Tom,
Actually, the procedure will run once a week for loading data into DW. That was the reason I was thinking of building temporary table through the same procedure which will load data into DW.
Regards,

Tom Kyte
April 21, 2003 - 10:52 pm UTC

just build it once, leave it there forever.

Keys referenced by other users

Sri, September 22, 2003 - 10:41 am UTC

Dear Tom,

The script that you have given for finding foreign keys works well within one schema. What if I have given a 'Grant Select, References on Emp to AnotherUser' and the Emp table gets referenced by a table created in AnotherUser's schema. I want to get this information also from the script. How can I get this (if possible, without using dba_cons_columns since my user doesn't have dba privileges). I am using Oracle 8.1.5

Thank you.

Tom Kyte
September 22, 2003 - 11:03 am UTC

if you don't have access, that will be a problem.

you can try the ALL_ views. they will show you what you are allowed to see.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.