Solved

query to find keys in a table

Posted on 2012-04-11
5
368 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
clob to char in oracle 3 89
create a nested synonym 4 40
Oracle encryption 12 59
Database Design Dilemma 6 65
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

740 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