Solved

SQL*PLUS: Getting Primary Keys for a table

Posted on 2001-06-21
9
2,674 Views
Last Modified: 2008-01-16
How do you get the fields that make up a primary key in a table with SQL*PLUS?  Also, how do you get the the names of the constraints?

Thx!
0
Comment
Question by:quaxar
[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
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 6

Expert Comment

by:crsankar
ID: 6216042
query the user_constraints or all_constraints data dictionar view

you can do a desc user_constraints to look at the columns in this table

0
 

Accepted Solution

by:
vijayakumar_vs earned 40 total points
ID: 6216048
First to get the primary key constrint name of a table, query the table USER_CONSTRAINTS. You can execute the following query in SQL*PLUS.

SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = <table name> AND CONSTRAINT_TYPE = 'P';

Constraint type "P" stands for PRIMATY KEY.

Once you get the constraint name use it to query all the columns that forms the Primary Key. The table USER_CONS_COLUMNS has to be queried for this. This table contains the details about all the Primary / Foreign constraints of all the tables in the schema. You can execute the following query.

SELECT COLUMN_NAME FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = <primary key constraint name> ;


0
 
LVL 2

Expert Comment

by:jammalk
ID: 6216062
You can identify the name of the primary key of a table using the column CONSTRAINT_TYPE in USER_CONSTRAINTS dictionary view & then you can identify the columns its composed of using USER_CONS_COLUMNS view.
The following is an example.

SQL> select b.constraint_name constraint,    b.column_name "column", b.position
  2  from user_constraints a, user_cons_columns b
  3  where a.constraint_name = b.constraint_name
  4  and a.constraint_type = 'P'
  5  and b.table_name='STAGE_CPDB'
  6* order by 3
SQL> /

CONSTRAINT           column                 POSITION
-------------------- -------------------- ----------
STAGE_CPDB_PK        ACTIVITY_TYPE                 1
STAGE_CPDB_PK        PBLSH_BRAND_ID                2
STAGE_CPDB_PK        PARNT_PROD_ID                 3
STAGE_CPDB_PK        SHIP_UNIT_CODE                4
STAGE_CPDB_PK        SHIP_UNIT_VERS_ID             5
STAGE_CPDB_PK        SALES_DEPT_CODE               6
STAGE_CPDB_PK        PBLSH_BRAND_BEG_DATE          7

7 rows selected.
0
Industry Leaders: 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!

 
LVL 2

Expert Comment

by:jammalk
ID: 6216064
You can identify the name of the primary key of a table using the column CONSTRAINT_TYPE in USER_CONSTRAINTS dictionary view & then you can identify the columns its composed of using USER_CONS_COLUMNS view.
The following is an example.

SQL> select b.constraint_name constraint,    b.column_name "column", b.position
  2  from user_constraints a, user_cons_columns b
  3  where a.constraint_name = b.constraint_name
  4  and a.constraint_type = 'P'
  5  and b.table_name='STAGE_CPDB'
  6* order by 3
SQL> /

CONSTRAINT           column                 POSITION
-------------------- -------------------- ----------
STAGE_CPDB_PK        ACTIVITY_TYPE                 1
STAGE_CPDB_PK        PBLSH_BRAND_ID                2
STAGE_CPDB_PK        PARNT_PROD_ID                 3
STAGE_CPDB_PK        SHIP_UNIT_CODE                4
STAGE_CPDB_PK        SHIP_UNIT_VERS_ID             5
STAGE_CPDB_PK        SALES_DEPT_CODE               6
STAGE_CPDB_PK        PBLSH_BRAND_BEG_DATE          7

7 rows selected.
0
 
LVL 2

Expert Comment

by:jammalk
ID: 6216066
You can identify the name of the primary key of a table using the column CONSTRAINT_TYPE in USER_CONSTRAINTS dictionary view & then you can identify the columns its composed of using USER_CONS_COLUMNS view.
The following is an example.

SQL> select b.constraint_name constraint,    b.column_name "column", b.position
  2  from user_constraints a, user_cons_columns b
  3  where a.constraint_name = b.constraint_name
  4  and a.constraint_type = 'P'
  5  and b.table_name='STAGE_CPDB'
  6* order by 3
SQL> /

CONSTRAINT           column                 POSITION
-------------------- -------------------- ----------
STAGE_CPDB_PK        ACTIVITY_TYPE                 1
STAGE_CPDB_PK        PBLSH_BRAND_ID                2
STAGE_CPDB_PK        PARNT_PROD_ID                 3
STAGE_CPDB_PK        SHIP_UNIT_CODE                4
STAGE_CPDB_PK        SHIP_UNIT_VERS_ID             5
STAGE_CPDB_PK        SALES_DEPT_CODE               6
STAGE_CPDB_PK        PBLSH_BRAND_BEG_DATE          7

7 rows selected.
0
 
LVL 2

Expert Comment

by:jammalk
ID: 6216076
You can identify the name of the primary key of a table using the column CONSTRAINT_TYPE in USER_CONSTRAINTS dictionary view & then you can identify the columns its composed of using USER_CONS_COLUMNS view.
The following is an example.

SQL> select b.constraint_name constraint,    b.column_name "column", b.position
  2  from user_constraints a, user_cons_columns b
  3  where a.constraint_name = b.constraint_name
  4  and a.constraint_type = 'P'
  5  and b.table_name='STAGE_CPDB'
  6* order by 3
SQL> /

CONSTRAINT           column                 POSITION
-------------------- -------------------- ----------
STAGE_CPDB_PK        ACTIVITY_TYPE                 1
STAGE_CPDB_PK        PBLSH_BRAND_ID                2
STAGE_CPDB_PK        PARNT_PROD_ID                 3
STAGE_CPDB_PK        SHIP_UNIT_CODE                4
STAGE_CPDB_PK        SHIP_UNIT_VERS_ID             5
STAGE_CPDB_PK        SALES_DEPT_CODE               6
STAGE_CPDB_PK        PBLSH_BRAND_BEG_DATE          7

7 rows selected.
0
 

Author Comment

by:quaxar
ID: 6216175
I couldn't get any of those to work.  Pretty weird!  I've tried yesterday trying something like jammalk said, but had no luck so I tried here.

All those primary keys queries return this:
no rows selected

And, I know for sure that my table has primary keys.

Here's what i did to make sure:
create table foo2 (field1 int, field2 int, primary key(field1));

SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'foo2' AND CONSTRAINT_TYPE = 'P';

select b.constraint_name constraint, b.column_name "column", b.position from user_constraints a, user_cons_columns b where a.constraint_name = b.constraint_name and a.constraint_type = 'P' and b.table_name='foo2';

Neither worked!  Weird!  I've done stuff like this a year or so ago and it worked fine.
0
 
LVL 6

Expert Comment

by:crsankar
ID: 6216197
Hi

SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'FOO2' AND CONSTRAINT_TYPE = 'P'

the problem is that you are using lower case for the tablename.

This will work.

cheers
crsankar

0
 

Author Comment

by:quaxar
ID: 6216269
Great thx vij :)

crsankar, I'm giving you 20 points too.  Please answer this question: Question for crsankar
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

630 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