Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL*PLUS: Getting Primary Keys for a table

Posted on 2001-06-21
9
Medium Priority
?
2,702 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
  • 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

886 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