Skip to Main Content
  • Questions

  • Primary Key created does not reflect in DBA_INDEXES

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: March 13, 2002 - 11:04 pm UTC

Last updated: March 25, 2004 - 10:36 am UTC

Version: 8.1.7.2.1

Viewed 1000+ times

You Asked

Hi Tom,

We would like to know more about the DBA_INDEXES and how it works internally.

Our problem here is we have a Primary Key created for a table but does not have corresponding information reflected in dba_indexes.

SQL> SELECT * FROM dba_constraints WHERE table_name = 'STD_SEM_HIST' and constraint_type in ('P','U');

OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ -
------------------------------
SEARCH_CONDITION
--------------------------------------------------------------------------------
R_OWNER R_CONSTRAINT_NAME DELETE_RU STATUS
DEFERRABLE
------------------------------ ------------------------------ ---------
-------- --------------
DEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE
--------- ------------- -------------- --- ---- -----------
IISDBM US_SMH_PK_AYSMMN P STD_SEM_HIST
ENABLED
NOT DEFERRABLE
IMMEDIATE VALIDATED USER NAME 28-JUN-1999
IISDBM UI_SMH_UK_AYSMMNCC U STD_SEM_HIST
ENABLED
NOT DEFERRABLE
IMMEDIATE VALIDATED USER NAME 28-JUN-1999

SQL> select * from dba_indexes
where index_name in (SELECT constraint_name FROM dba_constraints WHERE table_name = 'STD_SEM_HIST' and constraint_type in ('P','U'));

no rows selected

We tried to insert a duplicate record and we get the following error (which shows that the Primary Key is taking effect) :

SQL> insert into std_sem_hist
2 (CRSE_C,SEM_ACAD_Y,SEM_C,SEM_DEG_C,SEM_FAC_C,SEM_SEX_C,SEM_STS_C,STD_MATRIC_N)
3 values
4 ('BDG4', '1998/1999', '1', '101', '37', 'F', 'L', '954460M10')
5 ;
insert into std_sem_hist
*
ERROR at line 1:
ORA-00001: unique constraint (IISDBM.US_SMH_PK_AYSMMN) violated

Can you tell us why it's behaving this way ?

PS: We logged a TAR with the Oracle support, unfortunately they are not able to give us a valid explanation. But they did tell us what we already know...



and Tom said...

You are making the false assumption that a primary key will ALWAYS create an index -- it will not.

A primary key will use an existing indexe (be it unique or non-unique) to enforce itself.

Consider the following example:

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;
Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( a int, b int, c int );
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx on t(a,b);
Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_pk primary key(a);
Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select constraint_name, constraint_type from user_constraints;

CONSTRAINT_NAME C
------------------------------ -
T_PK P

ops$tkyte@ORA817DEV.US.ORACLE.COM> select index_name, uniqueness from user_indexes;

INDEX_NAME UNIQUENES
------------------------------ ---------
T_IDX NONUNIQUE


there, we have a primary key constraint and a single NON-UNIQUE index (that is not even on the primary key alone!!). In this case the primary key "kidnapped" the existing index and will utilize it to enforce unqiueness.

Now consider:



ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;
Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( a int, b int, c int );
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_pk primary key(a);
Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx on t(a,b);
Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select constraint_name, constraint_type from user_constraints;

CONSTRAINT_NAME C
------------------------------ -
T_PK P

ops$tkyte@ORA817DEV.US.ORACLE.COM> select index_name, uniqueness from user_indexes;

INDEX_NAME UNIQUENES
------------------------------ ---------
T_IDX NONUNIQUE
T_PK UNIQUE

ops$tkyte@ORA817DEV.US.ORACLE.COM

Here we do have a unique index created. The add constriant came BEFORE the create index -- hence there was no suitable index existing and the primary key created on for us. Notice that it created a unique index by default -- this is not always the case as shown below:


ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;

Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( a int, b int, c int );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_pk primary key(a) deferrable;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx on t(a,b);

Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select constraint_name, constraint_type from user_constraints;

CONSTRAINT_NAME C
------------------------------ -
T_PK P

ops$tkyte@ORA817DEV.US.ORACLE.COM> select index_name, uniqueness from user_indexes;

INDEX_NAME UNIQUENES
------------------------------ ---------
T_IDX NONUNIQUE
T_PK NONUNIQUE


Here, by using a deferrable constraint, we can see the primary key actually created a NON-UNIQUE index for us.




Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Woudn't it be beneficial to create a unique index for a primary key -avoid confusions

Ravi, March 14, 2002 - 10:14 am UTC

Hi Tom,
It was very useful. But, for administration/maintenance point-of-view, wouldn't it be beneficial (ofcourse, that's not the way it is, but..) to have a unique index automatically when we create a primary key?? This is instead of "kidnapping" (your word, I like it) an existing index (especially even when it is a composite one) and making it as a part of unique??
Thanks!
Ravi.

Tom Kyte
March 14, 2002 - 10:48 am UTC

No, no it wouldn't.

First of all -- the index NEED NOT BE UNIQUE, we have and support deferrable constraints.

Secondly, there are cases where having the index by non-unique is EXTREMELY advantageous (data warehouse where i must disable/enable the constraint sometimes. If I use a non-unique index, we can get rid of the constraint WITHOUT losing the index and put the constraint BACK without having to rebuild it).

There is nothing, but nothing that says "primary keys MUST build a unique constraint". As long as an index exists with the primary key columns in any order on the leading edge of the index -- that is more then good enough.



A reader, March 14, 2002 - 10:55 am UTC

What i understood so far was PRIMARY KEY enforce uniqueness by creating UNIQUE index on column , But i am now wrong.

Can you explain how PRIMARY KEY maintain uniqueness WITHOUT unique index ?

Tom Kyte
March 14, 2002 - 11:07 am UTC

The constraint is enforced procedurally by the server as always -- the index is used to speed access to the data that needs to be checked. The index is a performance thing with constraints, thats all. Whether it is unique or not -- it is a performance thing.

Index disappears when pramary key is disabled

Ravi Kumar, March 21, 2002 - 3:39 pm UTC

Hi Tom,

There is a primary key on a table with two columns. When I disable the primarykey and run queries, the index is not used. So, how to use that index even when I disable the primary key.

Thanks,
Ravi.

Tom Kyte
March 21, 2002 - 7:13 pm UTC 

use a non-unique index.  It is NOT that the index isn't used -- it is that the (unique) index is DROPPED when you disable the constraint.

This demonstrates one way to achieve that, just create the constraint as DEFERRABLE:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_pk primary key(x);

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select index_name, uniqueness from user_indexes;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
T_PK                           UNIQUE

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly explain
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where x = 1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   INDEX (UNIQUE SCAN) OF 'T_PK' (UNIQUE)



ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t disable constraint t_pk;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where x = 1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'T'



ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t drop constraint t_pk;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_pk primary key(x) DEFERRABLE;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select index_name, uniqueness from user_indexes;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
T_PK                           NONUNIQUE

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly explain
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where x = 1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   INDEX (RANGE SCAN) OF 'T_PK' (NON-UNIQUE)



ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t disable constraint t_pk;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where x = 1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   INDEX (RANGE SCAN) OF 'T_PK' (NON-UNIQUE)



ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off
ops$tkyte@ORA817DEV.US.ORACLE.COM>

"Primary Key created does not reflect in DBA_INDEXES", version 8.1.7.2.1

asake, July 12, 2002 - 8:56 am UTC

Extremely useful as always.

I have a problem in a DataW/H with deferrable constraints using Oracle 8.1.5.0 . After a dataload we ended up with duplicate rows and nulls being inserted in tables with composite primary keys. The tables all have the primary key as deferrable novalidate.

I understand that with deferrable constraints, Oracle creates a NON-UNIQUE INDEX which is what we have on these tables, the issue I have is why we can insert duplicates and nulls in columns defined as not null during table creation and are columns in the primary key.

Is this standard behaviour?

Thanks

Tom Kyte
July 12, 2002 - 9:57 am UTC

how did you do the load?
what is the status of the constraint in dba_constraints?

A reader, July 12, 2002 - 1:07 pm UTC

Hi Tom,

The data was loaded using Powermart. The status of all constraints in dba_constraints is enabled.

Asake

Tom Kyte
July 12, 2002 - 5:35 pm UTC 

I believe that powermart is loading the bad data -- it should be scrubbing it for you.  

They are using some of our data warehousing features that allow you to enable constraints -- but not validate them -- letting the database "trust" you did the right thing.  Powermart -- isn't doing the right thing.

consider:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_pk primary key(x) deferrable;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t disable constraint t_pk;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t enable novalidate constraint t_pk ;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

         X
----------
         1
         1

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1 );
insert into t values ( 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_PK) violated


ops$tkyte@ORA817DEV.US.ORACLE.COM> select status from user_constraints where constraint_name = 'T_PK';

STATUS
--------
ENABLED

ops$tkyte@ORA817DEV.US.ORACLE.COM> 


Powermart must be doing the same thing (enabling the constraint with NOVALIDATE) -- but they didn't scrub the data first.

Time to give them a buzz....

asake, July 15, 2002 - 10:48 am UTC

Hi Tom,

Thanks for the update.
Enabling a constraint "novalidate", should not allow new data that violates the constraint to be entered. If the constraint is also deferred, this means the data is only checked when the transaction commits. I don't understand how Powermart comes into play as Oracle is menat to enforce the integrity of the data. I have done a straight insertion into my test table and I can insert duplicates and nulls without using Powermart.

Kindly clarify: table created originally with composite primary key and primary key set as deferrable novalidate allows the insertion of nulls and duplicates?

Thanks



Tom Kyte
July 15, 2002 - 12:30 pm UTC 

Give me a test case including the CREATE TABLE.  What you describe now is different then before.  Before you said "after a load..." and I showed how that can happen

Prove to me with a cut and paste from sqlplus that what you say is correct.  show the query that proves the constraint is there and working.  Use my print_table routine like this:
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1035431863958 

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec print_table( 'select * from user_constraints where constraint_name = ''T_PK'' ' )
OWNER                         : OPS$TKYTE
CONSTRAINT_NAME               : T_PK
CONSTRAINT_TYPE               : P
TABLE_NAME                    : T_OLD
SEARCH_CONDITION              :
R_OWNER                       :
R_CONSTRAINT_NAME             :
DELETE_RULE                   :
STATUS                        : ENABLED
DEFERRABLE                    : DEFERRABLE
DEFERRED                      : IMMEDIATE
VALIDATED                     : NOT VALIDATED
GENERATED                     : USER NAME
BAD                           :
RELY                          :
LAST_CHANGE                   : 12-jul-2002 17:33:59
-----------------

PL/SQL procedure successfully completed.

so we can read it.  show us the primary key -- tell us the columns -- insert some duplicate data, commit and show us the outcome.

show me like I show you in short.

Excellent insight into Primary Key constraint use of indexes

Dave Anderson, August 01, 2002 - 8:37 am UTC

This was solid info into how PK constraints use indexes.



How to determine what index is used to enforce constraint.

David Fitzwater, September 17, 2002 - 6:20 pm UTC

On version 8.06, is there a view or method which will determine what index is enforcing the primary or alternate key uniqueness?

I use the technique a lot (i.e non unique index for enforcing constraint). It's really handy when other data is accessed via the primary key, but there must be other indexes (precluding iot). It would be handy to quickly access where this technique is used in those cases where one is considering some maintenance or changes to the underlying index.

Tom Kyte
September 17, 2002 - 8:13 pm UTC

In 9i -- there is a column in user_constraints that gives you this but not before.

Before that, see

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1360203050892 <code>

for something that will help you

is this the design?

reader, March 13, 2004 - 5:41 pm UTC

Why oracle has to drop the unique index when I disable the PK constraint? Thanks.

Tom Kyte
March 14, 2004 - 9:46 am UTC 

it doesn't "have to" but -- if it did not, why bother dropping the constraint?  The unique index would still make the stuff "unique"

ops$tkyte@ORA9IR2> create table t ( x int constraint x_pk primary key );
 
Table created.
 
ops$tkyte@ORA9IR2> select object_name, object_name from user_objects;
 
OBJECT_NAME                    OBJECT_NAME
------------------------------ ------------------------------
T                              T
X_PK                           X_PK
 
ops$tkyte@ORA9IR2> alter table t drop constraint x_pk keep index;
 
Table altered.
 
ops$tkyte@ORA9IR2> select object_name, object_name from user_objects;
 
OBJECT_NAME                    OBJECT_NAME
------------------------------ ------------------------------
T                              T
X_PK                           X_PK

PK can not use multi-column unique index

JW, March 25, 2004 - 10:36 am UTC

I opened TAR 3585361.994 with a test case because a PK would not use a unique multi-column index. It would use the index if it was not-unique.
I received the response that this is expected behavior. So keep in mind this will not work with a unique multi-column index.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.