SQL*PLUS: Getting Primary Keys for a table

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!
quaxarAsked:
Who is Participating?
 
vijayakumar_vsCommented:
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
 
crsankarCommented:
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
 
jammalkCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
jammalkCommented:
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
 
jammalkCommented:
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
 
jammalkCommented:
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
 
quaxarAuthor Commented:
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
 
crsankarCommented:
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
 
quaxarAuthor Commented:
Great thx vij :)

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.