Forum Stats

  • 3,751,461 Users
  • 2,250,366 Discussions
  • 7,867,433 Comments

Discussions

how to find if PK index was created implicitly on PK constraint creation

822042
822042 Member Posts: 2
edited Sep 19, 2012 10:43PM in General Database Discussions
Hi All,

I want to somehow find if PK index that is created on my table was created implicitly on PK constraint creation or PK index was created first by developer and then constraint was created.
Oracle Version: 10.2

Basically we have a situation, when we drop PK constraint it does not drop PK index for some. We want to find out how many such PK indexes exists which wont be dropped when PK constraint is dropped.
Command used to drop PK constraint:

alter table tab1 drop constraint tab1_pk; (note: we are not using drop index keyword at the end, but that is intentional).

Thanks!
Tagged:

Answers

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    bhav22 wrote:
    Hi All,

    I want to somehow find if PK index that is created on my table was created implicitly on PK constraint creation or PK index was created first by developer and then constraint was created.
    Oracle Version: 10.2

    Basically we have a situation, when we drop PK constraint it does not drop PK index for some. We want to find out how many such PK indexes exists which wont be dropped when PK constraint is dropped.
    Command used to drop PK constraint:

    alter table tab1 drop constraint tab1_pk; (note: we are not using drop index keyword at the end, but that is intentional).

    Thanks!
    does DBA_OBJECTS.CREATED for both table & index contain the same value?
    If they differ, then the objects were made at different times!
  • mbobak
    mbobak Member Posts: 1,342 Gold Badge
    If you don't specify the 'drop index' or 'keep index' syntax, then, if the index is unique, then when you disable or drop the PK, the index will be dropped. If the index is non-unique, and you disable or drop the PK, the index will be retained.

    Hope that helps,

    -Mark
  • Welcome to the forum!

    I believe that this Oracle Scratchpad article by Oracle ACE and noted author Jonathan Lewis will answer that question for you.
    http://jonathanlewis.wordpress.com/2012/04/19/drop-constraint/#comment-46140

    He mentioned this link when he answered my question in this thread
    What SYS tables (not views) contain column def'n of NULL/NOT NULL spec?

    10293268

    My question was about how a NULLABLE column is marked NOT NULLABLE when a primary key constraint is added using that column. If you check ALL_TAB_COLS the column will show as NOT NULLABLE. But then if you drop the constraint and check ALL_TAB_COLS again the column now shows the original NULLABLE value.

    My question was where the original settiing is stored.
  • 822042
    822042 Member Posts: 2
    edited Sep 19, 2012 10:43PM
    Thank you rp0428 and others.

    Post by rp0428 has the answer we want! It really helped me find the link between PK index and PK constraint.

    -- if the bitand returns 4097, index will be dropped when constraint is dropped
    select do.object_name, do.object_id, bitand(ind$.property,4097), ind$.property
    from sys.ind$ ind$
    , dba_objects do
    where do.owner='SCOTT'
    and do.object_name in ('TEST1_PK','TEST2_PK')
    and do.object_id=ind$.obj#

    Thanks!

    Edited by: bhav22 on 19/09/2012 19:43
This discussion has been closed.