Oracle Info of Primay Key in a Table
in place July 5, 2000, last modified July 5, 2000

---

Description

To get information of primary keys of a table use the following tables and statements.

Related Tables

1. USER_CONSTRAINTS
   SQL> desc user_constraints;
   Name Null? Type
   ------------------------------- -------- ----
   OWNER NOT NULL VARCHAR2(30)
   CONSTRAINT_NAME NOT NULL VARCHAR2(30)
   CONSTRAINT_TYPE VARCHAR2(1)
   TABLE_NAME NOT NULL VARCHAR2(30)
   SEARCH_CONDITION LONG
   R_OWNER VARCHAR2(30)
   R_CONSTRAINT_NAME VARCHAR2(30)
   DELETE_RULE VARCHAR2(9)
   STATUS VARCHAR2(8)
   
   
2. USER_CONS_COLUMNS
   SQL> desc user_cons_columns;
   Name Null? Type
   ------------------------------- -------- ----
   OWNER NOT NULL VARCHAR2(30)
   CONSTRAINT_NAME NOT NULL VARCHAR2(30)
   TABLE_NAME NOT NULL VARCHAR2(30)
   COLUMN_NAME NOT NULL VARCHAR2(30)
   POSITION NUMBER
   
   

DML Statements with Example Table RSP_CRR_PART

1. SQL> select * from user_constraints where table_name='RSP_CRR_PART';
   
   OWNER: VPMWEB
   CONSTRAINT_NAME: SYS_C009251
   CONSTRAINT_TYPE: P
   TABLE_NAME RSP_CRR_PART:
   SEARCH_CONDITION:
   R_OWNER:
   R_CONSTRAINT_NAME:
   DELETE_RULE:
   STATUS: ENABLED
   
   
2. SQL> select * from user_cons_columns where constraint_name='SYS_C009251';
   OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
   ---------------------------------------
   
   VPMWEB SYS_C009251 RSP_CRR_PART VALUE_CODE 3
   VPMWEB SYS_C009251 RSP_CRR_PART PART_NO 1
   VPMWEB SYS_C009251 RSP_CRR_PART PART_VER 2
   
   * The RSP_CRR_PART table has three key attributes, VALUE_CODE, PART_NO, PART_VER;

---

Knowledge & Engineering Databases (c) copyright Namchul Do, 2000