Issue with getting primary key column in Oracle

Question

This is a common question, I found few links with regards to this

None of those queries returns value in my case. What could be the possible issue?

    SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_type = 'P'
AND cols.table_name LIKE '%WORKORDERSPEC%'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
AND cons.owner =  'DEV_WWM_WEBAPP_SIT1' 
ORDER BY cols.table_name, cols.position;

Following is the Create Table DDL

CREATE TABLE DEV_WWM_WEBAPP_SIT1.WORKORDERSPEC
  (
    WORKORDERSPECID NUMBER NOT NULL ,
    ALNVALUE        VARCHAR2(4000 CHAR) ,
    ASSETATTRID     VARCHAR2(100 BYTE) NOT NULL ,
    WONUM           VARCHAR2(15 BYTE) NOT NULL ,
    CHANGEBY        VARCHAR2(30 BYTE) NOT NULL ,
    CHANGEDATE DATE NOT NULL ,
    CLASSSTRUCTUREID    VARCHAR2(20 BYTE) NOT NULL ,
    DISPLAYSEQUENCE     NUMBER NOT NULL ,
    LINKEDTOATTRIBUTE   VARCHAR2(100 BYTE) ,
    LINKEDTOSECTION     VARCHAR2(20 BYTE) ,
    MEASUREUNITID       VARCHAR2(25 BYTE) ,
    NUMVALUE            NUMBER(30, 10) ,
    ORGID               VARCHAR2(8 BYTE) NOT NULL ,
    SECTION             VARCHAR2(20 BYTE) ,
    SITEID              VARCHAR2(8 BYTE) NOT NULL ,
    REFOBJECTID         NUMBER NOT NULL ,
    REFOBJECTNAME       VARCHAR2(30 BYTE) ,
    CLASSSPECID         NUMBER ,
    MANDATORY           NUMBER NOT NULL ,
    TABLEVALUE          VARCHAR2(254 BYTE) ,
    ROWSTAMP            VARCHAR2(40 BYTE) NOT NULL ,
    GBSREQDCONDITIONNUM VARCHAR2(30 BYTE) ,
    GBSROCONDITIONNUM   VARCHAR2(30 BYTE) ,
    NBNVALUEREMOVED     NUMBER DEFAULT NULL NOT NULL ,
    NBNTARGETOBJECT     VARCHAR2(30 CHAR) ,
    NBNTARGETATTRIBUTE  VARCHAR2(50 CHAR) ,
    GBSSPECDATETIMEVALUE DATE ,
    GBSSPECDATEVALUE DATE
  )
  LOGGING TABLESPACE "MAXDATA_SIT1" PCTFREE 10 INITRANS 1 STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT
  )
  CREATE UNIQUE INDEX DEV_WWM_WEBAPP_SIT1.WORKORDERSPEC_NDX ON DEV_WWM_WEBAPP_SIT1.WORKORDERSPEC
  (
    WORKORDERSPECID ASC
  )
  LOGGING TABLESPACE "MAXDATA_SIT1" PCTFREE 10 INITRANS 2 STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT
  )
  CREATE UNIQUE INDEX DEV_WWM_WEBAPP_SIT1.WORKORDERSPEC_NDX1 ON DEV_WWM_WEBAPP_SIT1.WORKORDERSPEC
  (
    WONUM ASC,
    SITEID ASC,
    ASSETATTRID ASC,
    SECTION ASC
  )
  LOGGING TABLESPACE "MAXDATA_SIT1" PCTFREE 10 INITRANS 2 STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT
  )
CREATE INDEX DEV_WWM_WEBAPP_SIT1.WORKORDERSPEC_NDX2 ON DEV_WWM_WEBAPP_SIT1.WORKORDERSPEC
  (
    REFOBJECTID ASC
  )
  LOGGING TABLESPACE "MAXDATA_SIT1" PCTFREE 10 INITRANS 2 STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT
  )

Hope this helps

Assuming that your table actually does have a primary key constraint (it would be really helpful to show a complete example where you define a table, define the constraint, and show us exactly what query you're running) I would guess that you're not specifying the table name and/or schema name correctly. Assuming you're not using case-sensitive identifiers, the table name and schema name in the data dictionary will be stored in upper case. Is that how you are specifying the strings in your query? — Justin Cave, Aug 05 '15 at 06:17
Please edit your question and add the (**exact**) `CREATE TABLE` statement for the table in question — , Aug 05 '15 at 06:19

Jeffrey Kemp · Answer 1 · 2015-08-05T08:14:28.027

3

Your query is searching for a Primary Key constraint:

WHERE cons.constraint_type = 'P'

But your DDL shows no primary key constraint was created. You've only shown DDL for the table and some indexes.

A unique index does not create a Primary Key constraint - however, a Primary Key constraint does (usually) create a unique index.

edited Aug 05 '15 at 08:14

answered Aug 05 '15 at 08:04

Jeffrey Kemp

59,135
14
106
158

score 0 · Answer 2 · answered Aug 05 '15 at 08:09

If there was a constraint type = to 'P' perhaps that query would work.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE WORKORDERSPEC
  (
    WORKORDERSPECID NUMBER NOT NULL ,
    ALNVALUE        VARCHAR2(4000 CHAR) ,
    ASSETATTRID     VARCHAR2(100 BYTE) NOT NULL ,
    WONUM           VARCHAR2(15 BYTE) NOT NULL ,
    CHANGEBY        VARCHAR2(30 BYTE) NOT NULL ,
    CHANGEDATE DATE NOT NULL ,
    CLASSSTRUCTUREID    VARCHAR2(20 BYTE) NOT NULL ,
    DISPLAYSEQUENCE     NUMBER NOT NULL ,
    LINKEDTOATTRIBUTE   VARCHAR2(100 BYTE) ,
    LINKEDTOSECTION     VARCHAR2(20 BYTE) ,
    MEASUREUNITID       VARCHAR2(25 BYTE) ,
    NUMVALUE            NUMBER(30, 10) ,
    ORGID               VARCHAR2(8 BYTE) NOT NULL ,
    SECTION             VARCHAR2(20 BYTE) ,
    SITEID              VARCHAR2(8 BYTE) NOT NULL ,
    REFOBJECTID         NUMBER NOT NULL ,
    REFOBJECTNAME       VARCHAR2(30 BYTE) ,
    CLASSSPECID         NUMBER ,
    MANDATORY           NUMBER NOT NULL ,
    TABLEVALUE          VARCHAR2(254 BYTE) ,
    ROWSTAMP            VARCHAR2(40 BYTE) NOT NULL ,
    GBSREQDCONDITIONNUM VARCHAR2(30 BYTE) ,
    GBSROCONDITIONNUM   VARCHAR2(30 BYTE) ,
    NBNVALUEREMOVED     NUMBER DEFAULT NULL NOT NULL ,
    NBNTARGETOBJECT     VARCHAR2(30 CHAR) ,
    NBNTARGETATTRIBUTE  VARCHAR2(50 CHAR) ,
    GBSSPECDATETIMEVALUE DATE ,
    GBSSPECDATEVALUE DATE
  )
;
   CREATE UNIQUE INDEX WORKORDERSPEC_NDX ON WORKORDERSPEC
  (
    WORKORDERSPECID ASC
    )
;
    CREATE UNIQUE INDEX WORKORDERSPEC_NDX1 ON WORKORDERSPEC
  (
    WONUM ASC,
    SITEID ASC,
    ASSETATTRID ASC,
    SECTION ASC
  )
;
    CREATE INDEX WORKORDERSPEC_NDX2 ON WORKORDERSPEC
  (
    REFOBJECTID ASC
  )
;

Query 1:

select OWNER , CONSTRAINT_NAME , CONSTRAINT_TYPE ,  TABLE_NAME 
from user_constraints

Results:

|        OWNER | CONSTRAINT_NAME | CONSTRAINT_TYPE |    TABLE_NAME |
|--------------|-----------------|-----------------|---------------|
| USER_4_78AA2 |  SYS_C001048129 |               C | WORKORDERSPEC |
| USER_4_78AA2 |  SYS_C001048128 |               C | WORKORDERSPEC |
| USER_4_78AA2 |  SYS_C001048127 |               C | WORKORDERSPEC |
| USER_4_78AA2 |  SYS_C001048126 |               C | WORKORDERSPEC |
| USER_4_78AA2 |  SYS_C001048125 |               C | WORKORDERSPEC |
| USER_4_78AA2 |  SYS_C001048124 |               C | WORKORDERSPEC |
| USER_4_78AA2 |  SYS_C001048123 |               C | WORKORDERSPEC |
| USER_4_78AA2 |  SYS_C001048122 |               C | WORKORDERSPEC |
| USER_4_78AA2 |  SYS_C001048121 |               C | WORKORDERSPEC |
| USER_4_78AA2 |  SYS_C001048120 |               C | WORKORDERSPEC |
| USER_4_78AA2 |  SYS_C001048119 |               C | WORKORDERSPEC |
| USER_4_78AA2 |  SYS_C001048118 |               C | WORKORDERSPEC |
| USER_4_78AA2 |  SYS_C001048117 |               C | WORKORDERSPEC |

Issue with getting primary key column in Oracle

2 Answers2