Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2708
  • Last Modified:

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!
0
quaxar
Asked:
quaxar
  • 4
  • 2
  • 2
  • +1
1 Solution
 
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
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now