Hi Tom,

my question is about constraints in combination with indexes.
I have a big amount of tables in multiple environments with primary keys and corresponding indexes like this:

create table Articles
(
  company_id          number,
  article_id          varchar2(30),
  article_description varchar2(500)
);

alter table Articles
  add constraint Articles_PK primary key (COMPANY_ID, ARTICLE_ID) using index;

They have a good primary key, but a less good ordered index for querying. The queries contain article_id and company_id.
Instead of creating new indexes with a better order, i want to change the existing one´s, in order not to unnecessarily increase the number of unuesed indexes.

All environments are running 24/7 and need their uniqueness on the tables.

So i´ve readed the following article of Chris from July:
https://blogs.oracle.com/sql/post/how-to-modify-constraints-in-oracle-database

After this and some other research, i´ve decided to change them the following way:

create unique index Articles_IDX_TMP on Articles (ARTICLE_ID, COMPANY_ID);
alter table Articles modify constraint Articles_PK using index Articles_IDX_TMP;
alter index Articles_IDX_TMP rename to Articles_PK;

This keeps the uniqueness, keeps the order ot the primary key (which is important for other reasons),
doesn´t drop any foreign key constraints which use the primary key, drops the original index and replaces the new ordered index for the old one.

Good till now.

After this, i noticed following difference in dropping a primary key constraint.

When i dropped an primary key constraint before, the corresponding index was dropped automatically.
I executed the following command to drop the constraint:

alter table Articles drop constraint Articles_PK cascade;

and the database fired two commands autmatically:

drop index Articles_PK;
alter table Articles drop constraint Articles_PK cascade;

I´ve seen this after logging all ddl with a trigger on schema level (after ddl on database).

After my above three changes to replace the index of a primary key constraint,
the command:

alter table Articles drop constraint Articles_PK cascade;

isn´t dropping the index (index: Articles_PK) automatically any more.


My question to you is:
Is there any command to alter the index after this he will be automatically dropped with the drop primary key constraint togehter like before?
How can i reorder the index (not primary key) and achieve the same behavior when dropping the primary key, the index will be dropped automatically with it?
Or is there a better way to change them i haven´t found?

Thanks for your help, and a stress-free week.

create table articles
(
  company_id          number,
  article_id          varchar2(30),
  article_description varchar2(500)
);

alter table articles
  add constraint articles_pk primary key (company_id, article_id) using index;
  
create unique index articles_idx_tmp on articles (article_id, company_id);

select index_name, constraint_index
from   user_indexes
where  table_name = 'ARTICLES';
/*
INDEX_NAME          CONSTRAINT_INDEX    
ARTICLES_PK         YES                 
ARTICLES_IDX_TMP    NO                  
*/

alter table articles modify constraint articles_pk using index articles_idx_tmp;

select index_name, constraint_index
from   user_indexes
where  table_name = 'ARTICLES';
/*
INDEX_NAME          CONSTRAINT_INDEX    
ARTICLES_IDX_TMP    NO                  
*/

alter index articles_idx_tmp rename to articles_pk;

select index_name, constraint_index
from   user_indexes
where  table_name = 'ARTICLES';
/*
INDEX_NAME     CONSTRAINT_INDEX    
ARTICLES_PK    NO   
*/

alter table articles 
  drop constraint articles_pk
  cascade drop index;

select index_name, constraint_index
from   user_indexes
where  table_name = 'ARTICLES';

--no rows selected