Solved

query to find keys in a table

Posted on 2012-04-11
5
365 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
  • 2
  • 2
5 Comments
 
LVL 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB.Net - CSV to Oracle table 4 70
Retreiving column names in Windows but not in Unix 11 47
Oracle Listener Not Starting 11 44
Use of Exception to end a Loop 3 32
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

777 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