Solved

query to find keys in a table

Posted on 2012-04-11
5
370 Views
Last Modified: 2012-06-27
hi

i have this query which tells what are the primary keys in a table

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = 'PERSON_DATA'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

Any one care to explain what are the tables : all_constraints and all_cons_columns
and what is cons.constraint_type = 'P'

Thanks
0
Comment
Question by:royjayd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 37833393
>>all_constraints
>>all_cons_columns
>>cons.constraint_type = 'P'

All of this is in the docs:
http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_1046.htm

http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_1044.htm
0
 

Author Comment

by:royjayd
ID: 37833409
slightwv

A simple one or two liners with a layman explanation would have helped :-)
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37833421
I'm not sure I could have explained it better than the docs do.
0
 
LVL 32

Accepted Solution

by:
awking00 earned 200 total points
ID: 37833483
all_constraints and all_cons_columns are system dictionay views containing metadata of the database. The all_ prefix allows you to get information on objects that you own or have access to (the dba_ prefix is for all objects and the user_prefix is only for objects you own).
The constraint_type values available are:
C - Check constraint on a table
P - Primary key
U - Unique key
R - Referential integrity
V - With check option, on a view
O - With read only, on a view
H - Hash expression
F - Constraint that involves a REF column
S - Supplemental logging

Some additional links -
http://psoug.org/reference/constraints.html
http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1042.htm
0
 

Author Comment

by:royjayd
ID: 37837505
thanks.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question