Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL*PLUS: Getting Primary Keys for a table

Posted on 2001-06-21
9
Medium Priority
?
2,694 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 160 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

719 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