Re: PK constraint and underlying index behaviour

Thank you Jonathan.
So basically if the automatic index is unique it gets attached to the constraints by Oracle internally and thus will be dropped along with it. Non unique ones stays intact.
Thank you.

On Fri, 8 Jul 2022, 12:03 pm Jonathan Lewis, <jlewisoracle_at_gmail.com> wrote:

>
> It's expected Behaviour.
>
> The note at
> https://jonathanlewis.wordpress.com/2012/04/19/drop-constraint/ doesn't
> address this directly, but gives you some important background. Do read the
> comments as well.
>
> Regards
> Jonathan Lewis
>
>
> On Fri, 8 Jul 2022 at 06:15, Pap <oracle.developer35_at_gmail.com> wrote:
>
>> Creating index and constraint separately outside create table statement
>> is working fine.
>>
>> I was stumped about the behaviour while creating constraint as part of
>> create table statement only and then disabling+re-enabling the constraint
>> making the index global . So wanted to check if it's expected or some
>> specific settings making this happen?
>>
>> On Thu, 7 Jul 2022, 9:47 pm Pap, <oracle.developer35_at_gmail.com> wrote:
>>
>>> Hello, I am seeing some odd behavior. Its version 19C of Oracle.
>>>
>>> If we create primary key constraint as part of create table statement
>>> then a primary key index(which is local) gets created automatically behind
>>> the scene which is okay.
>>>
>>> But when we disable that constraint, the underlying index gets dropped
>>> automatically. And the most odd thing is when , we re-enable the
>>> constraint, it again re-create the underlying index but not as local , its
>>> rather created as a global index. Is this expected behavior?
>>>
>>>
>>> ---Creating a table
>>>
>>> CREATE TABLE SCOTT.PART_TAB
>>>
>>> ( PART_DATE DATE, EID NUMBER(10),
>>>
>>> CONSTRAINT PART_TAB_IND PRIMARY KEY (EID, PART_DATE) USING INDEX LOCAL)
>>>
>>> PARTITION BY RANGE (PART_DATE)
>>>
>>> (
>>>
>>> PARTITION DAY_11_JUL VALUES LESS THAN (TO_DATE(' 2019-07-12 00:00:00',
>>> 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
>>>
>>> PARTITION DAY_12_JUL VALUES LESS THAN (TO_DATE(' 2019-07-13 00:00:00',
>>> 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
>>>
>>> PARTITION p_today VALUES LESS THAN (TO_DATE('2020-11-25 00:00:00',
>>> 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
>>>
>>> );
>>>
>>> Table created.
>>>
>>> ----Checking Index status ----------
>>>
>>> SQL> select index_name, partitioned from user_indexes where index_name =
>>> 'PART_TAB_IND';
>>>
>>> INDEX_NAME PARTITIONED
>>>
>>> --------------------------------- -------------------------------
>>>
>>> PART_TAB_IND YES
>>>
>>>
>>>
>>> ----- Disabling the constraint----------
>>>
>>> SQL> alter table PART_TAB DISABLE CONSTRAINT PART_TAB_IND;
>>>
>>> Table altered.
>>>
>>> ----Index status ----------
>>>
>>> SQL> select index_name, partitioned from user_indexes where index_name =
>>> 'PART_TAB_IND';
>>>
>>> no rows selected
>>>
>>> ----- Enabling the constraint----------
>>>
>>> SQL> alter table PART_TAB ENABLE CONSTRAINT PART_TAB_IND;
>>>
>>> Table altered.
>>>
>>> ----Checking Index status
>>>
>>> SQL> select index_name, partitioned from user_indexes
>>>
>>> 2 where index_name = 'PART_TAB_IND';
>>>
>>> INDEX_NAME PARTITIONED
>>>
>>> --------------------------------- -------------------------------
>>>
>>> PART_TAB_IND NO
>>>
>>>
>>>
>>>
>>>
>>>
>>>

--
http://www.freelists.org/webpage/oracle-l